Copy legacy Oracle tables with SQL*Plus

You may be running Oracle 10g Release 2, but the applications you're supporting may have been written much earlier. Tables developed in versions of Oracle prior to 8i often used the LONG datatype to store large amounts of text.

With the introduction of Large Object (LOB) datatypes, the LONG and LONG RAW datatypes were deprecated. If you need to make a copy of a table that uses LONG, the CREATE TABLE AS SELECT syntax won't work; you'll get this error: ORA-00997: illegal use of LONG datatype.

You could export the table and import it, but that's a lot of work. The COPY command in SQL*Plus can still copy such a table with a single command. Here is the format of COPY (note the use of the continuation character, -, to break up the line):

COPY FROM user/pw@dblink TO user/pw@dblink CREATE tablename -

USING select-statement;

Be sure to use SET LONG first to cover the length of the data in the LONG column and avoid truncated data.

The downside is that the COPY command was frozen in functionality at version 8.0 of Oracle. It can only copy tables consisting of the following datatypes: CHAR, DATE, LONG, NUMBER, and VARCHAR2. New datatypes added in versions 8i and later are not supported. This is purely a legacy solution.

The code below shows the CREATE TABLE error and the successful COPY command.

SQL> CREATE TABLE my_views AS
  2     SELECT * FROM user_views;
   SELECT * FROM user_views
          *
ERROR at line 2:
ORA-00997: illegal use of LONG datatype



SQL> SET LONG 100000
SQL> COPY FROM hr/hr@orcl CREATE my_views -
>USING select * from user_views;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 100000. (long is 100000)
Table MY_VIEWS created.

   1 rows selected from hr@orcl.
   1 rows inserted into MY_VIEWS.
   1 rows committed into MY_VIEWS at DEFAULT HOST connection.

SQL> descmy_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER(38)
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER(38)
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER(38)
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)

SQL> spool off