SQLPLUS COPY Command to copy tables across/within DB, useful for LONG data types.

Tables with LONG data type cannot be moved or copied to/from one tablespace/schema/database to another tablespace/schema/database using CTAS or even sql INSERT statement. It would fail with below error:

ORA-00997: illegal use of LONG datatype

So whats the solution. Either you could do export/import or sqlplus COPY command.It works like a charm. Here is a demonstration how I failed and how sqlplus COPY helped!!

Creating a table with LONG datatype.

SQL> create table copy_example (col1 varchar2(10), col2 long);

Table created.

SQL> insert into copy_example values ('11','10000000000000000000000000000000000000000000000000');

1 row created.

SQL> commit;

Commit complete.

Trying to CTAS:

SQL> create table copy_example_2 as select * from copy_example;

create table copy_example_2 as select * from copy_example

                                      *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Then Tried doing INSERT to pull LONG datatype to a different table:

SQL> create table copy_example_2 (col1 varchar2(10), col2 long);

Table created.

SQL> insert into copy_example_2 select * from copy_example;

insert into copy_example_2 select * from copy_example

                                  *

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

Failing too!!!

Lets see how SQLPLUS COPY command works then. You can copy tables between two databases. I’m using here same database names, you can use it for different databases as long as you have TNS entry on the source box.
Copy command support only CHAR, DATE, LONG, NUMBER andVARCHAR2 data types. It does not support any new data types.

SQL> drop table copy_example_2;

Table dropped.

SQL> copy from scott/tiger@LAB  to scott/tiger@LAB create copy_example_2 using select * from copy_example;

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 COPY_EXAMPLE_2 created.

   1 rows selected from scott@lab

   1 rows inserted into COPY_EXAMPLE_2.

   1 rows committed into COPY_EXAMPLE_2 at lab@lab

SQL> select * from system.copy_example_2;

COL1       COL2

---------- --------------------------------------------------------------------------------

11         10000000000000000000000000000000000000000000000000

Everything copied over!!! J