How to Copy Data from a Table with a LONG Column into an Existing Table

출처:  https://metalink.oracle.com/



How to Copy Data from a Table with a LONG Column into an Existing Table



Purpose:
========
The purpose of this article is to provide a working example of how to COPY data from a table with a LONG column into another existing table with the same structure definition.  This document is especially useful for helping avoid an ORA-997 error as you try to achive the above objective.



Scope & Application:
====================
This article is intended for use by anyone who is receiving an ORA-997 error when they try to create a new table with a LONG column from an existing table using the 'Create Table As Select' (CTAS) command.

You receive the same ORA-997 error even when you try to INSERT data from the table with a LONG column into another table with a LONG column using the INSERT INTO ... command.

The only way to resolve this problem is to use the SQL*Plus COPY command as discussed in this article.

This article is applicable for Oracle versions 7.3.4 through 8.1.X.



How to Copy Data from a Table with a LONG Column into an Existing Table:
========================================================================
 
Example:
--------

 $ sqlplus scott/tiger@otcsol1_v734

 SQL> create table le 
      (c1 number,
       c2 long);

 SQL> desc le

      Name                            Null?    Type
      ------------------------------- -------- ----
      C1                                       NUMBER
      C2                                       LONG

 SQL> insert into le values ( 123, 'long column values');

 SQL> commit;

 SQL> select * from le;

        C1 C2
     ----- --------------------------------------------------
       123 long column values

 SQL> create table le2
     (c1 number,
      c2 long);

 SQL> desc le2
 
      Name                            Null?    Type
      ------------------------------- -------- ----
      C1                                       NUMBER
      C2                                       LONG


 SQL> copy from scott/tiger@otcsol1_v734 to scott/tiger@otcsol1_v734 -
      > append le2 -
      > using select * from le;

     Array fetch/bind size is 15. (arraysize is 15)
     Will commit when done. (copycommit is 0)
     Maximum long size is 80. (long is 80)
     1 rows selected from scott@otcsol1_v734.
     1 rows inserted into LE2.
     1 rows committed into LE2 at scott@otcsol1_v734.



Notes:
------
o  You must use the connect string in the FROM and TO clauses of the COPY command as shown above.

o  You must use the "-" ( dash) at the end of each line if you want to break  the command into a number of lines.  Otherwise, the COPY command may fail.

o  This has been tested using a V734 connect string as well as other releases.

     SQL> select * from le2;

            C1 C2
         ----- --------------------------------------------------
           123 long column values



References:
===========
SQL*Plus: User's Guide and Reference