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