Skip to main content

Search

Welcome to Tibero GTS!

Long datatype table replication

Document Type | Technical Information

Category | Administration

Applicable Product Version | 7FS02PS

Document Number | TADTI075

 

Overview

This document describes how to copy tables that have columns with the LONG data type.

There are two methods to copy LONG column data: using the TO_LOB function to copy it as a LOB column type, or using a PL/SQL block to copy it as a VARCHAR type.

 
 

Method

Replication using the to_lob function

The to_lob function is used to convert LONG type data to CLOB type when copying to a table.

SQL> Create table long_test (a long);

Table 'LONG_TEST' created.

SQL> insert into long_test values('vijay kaushik');

1 row inserted.

SQL> commit;

Commit completed.

SQL> create table lob_test (a clob);

Table 'LOB_TEST' created.

SQL> insert into lob_test select to_lob(a) from long_test;

1 row inserted.

SQL> commit;

Commit completed.

 

Replication using a PL/SQL block

SQL> create table long_test2 (x int, y long);

Table 'LONG_TEST2' created.

SQL> create table varchar_test2 (x int, y varchar(65532));

Table 'VARCHAR_TEST2' created.

SQL> insert into long_test2 values(1,'vijay kaushik');

1 row inserted.

SQL> commit;

Commit completed.


SQL> begin
     for i in (select * from long_test2)
       loop
         insert into varchar_test2 values(i.x, i.y);
         commit;
       end loop;
     end;
     /
PSM completed.

 

Note

Only data up to the maximum length of the varchar data type (65523 bytes) can be copied.

 
When converting LONG data to another type using a simple INSERT statement, an error may occur.
In this case, you can avoid conversion errors by copying the data row by row using a PL/SQL block.
SQL> CREATE TABLE long_test
  2  (x INT, y LONG);

Table created.

Elapsed: 00:00:01.14
SQL> CREATE TABLE varchar_test
  2  (x INT, y VARCHAR2(4000));

Table created.

Elapsed: 00:00:01.14
SQL> INSERT INTO long_test VALUES(1,'vijay kaushik');

1 row created.

Elapsed: 00:00:01.14
SQL> COMMIT;

Commit complete;

-- Type conversion error occurs with a simple INSERT statement\
Elapsed: 00:00:01.14
SQL> INSERT INTO varchar_test SELECT * FROM long_test;
TBR-11021: Error occurred during type casting.

-- Replication using a PL/SQL block
Elapsed: 00:00:01.34
SQL> BEGIN
  2    FOR i IN (SELECT * FROM long_test)
  3    LOOP
  4    INSERT INTO varchar_test VALUES(i.x, i.y);
  5    END LOOP;
  6    END;
SQL> /

PL/SQL procedure successfully completed.
Elapsed: 00:00:01.62

 

Related to