Thursday, November 26, 2009

Copying Long datatypes

The column's data with datatype long can be copied by 3 different methods
1)Use copy command
2)use to_lob
3)use PL/SQL

METHOD 1:
==========
SQL> copy from Scott/tiger@orcl create varchar_test using select * from long_test
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table VARCHAR_TEST created.

1 rows selected from Scott@orcl.
1 rows inserted into VARCHAR_TEST.
1 rows committed into VARCHAR_TEST at DEFAULT HOST connection.

METHOD 2:
==========
SQL> Create table long_test (a long);
Table created.
Elapsed: 00:00:02.68
SQL> insert into long_test values('vijay kaushik');
1 row created.
Elapsed: 00:00:01.09
SQL> commit;
Commit complete.
Elapsed: 00:00:01.09
SQL> create table lob_test (a clob);
Table created.
Elapsed: 00:00:01.29
SQL> insert into lob_test select to_lob(a) from long_test;
1 row created.
Elapsed: 00:00:01.09
SQL> commit;
Commit complete.

METHOD 3:
=========
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.11
SQL> insert into long_test values(1,'vijay kaushik');
1 row created.
Elapsed: 00:00:01.09
SQL> commit;
Commit complete.
Elapsed: 00:00:01.11
SQL> insert into varchar_test select * from long_test;
insert into varchar_test select * from long_test
*ERROR at line 1:
ORA-00997: illegal use of LONG datatype
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

No comments: