개요

방법

to_lob 함수를 이용한 복제

to_lob 함수는 LONG 타입 데이터를 CLOB 타입으로 변환하여 테이블에 복사할 때 사용합니다.

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.

PL/SQL 블록을 이용한 복제

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.

주의

varchar 데이터 타입의 최대 길이(65523bytes) 까지의 데이터만 복사가 가능합니다.

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;

-- 단순 INSERT 시 타입 변환 오류 발생\
Elapsed: 00:00:01.14
SQL> INSERT INTO varchar_test SELECT * FROM long_test;
TBR-11021: Error occurred during type casting.

-- PL/SQL 블록으로 복제
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