asked on

How to solve ORA-00997: illegal use of LONG datatype while copying tables

I need to copy a table identicaly to another. I use this command to do it : CREATE TABLE target AS SELECT * FROM source

But i've got an ORA-00997 error : illegal use of LONG datatype, because my table has a type long field.

Here is the description of the source table :

CREATE TABLE "source"
("LDKEY" NUMBER NOT NULL,
"LDOWNERTABLE" VARCHAR2(18 byte) NOT NULL,
"LDOWNERCOL" VARCHAR2(18 byte) NOT NULL,
"LDTEXT" LONG,
"ROWSTAMP" VARCHAR2(40 byte)
)

Am i doing wrong ? Is there another way to do this copy ?

Regards,

Cyril

ASKER CERTIFIED SOLUTION

vc01778

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ora_Techie

Here's a simple example from asktom.oracle.com:

Moving LONGS Around
The question "How do I move a LONG column from one table to another" comes up from time to time. The following shows how to use SQLPlus to copy longs from table to table or database to database. Note that this does not work for long raws. There simply are no good solutions for long raws without coding C.
The sql*plus copy command will do it for longs. You need a sql*net connect string that loops back to your local database (not a dblink, a sqlplus connect string, you need to be able to "sqlplus scott/tiger@yourdatabase"...

For example, I just:

create table foo
( The_Whole_View varchar2(65),
TextLength number,
TheText Long )
/

which is a table, sort of like all_views (which has a long)... Then I:

SQL> copy from tkyte/tkyte@aria insert foo (the_whole_view, textlength, thetext ) using select owner||'.'||view_name, text_length, text from all_views;

So the sqlplus command transformed the table for me (the columns are not the
same). Also, I could have used a where clause to pick off just some rows.
You'll want to set

set arraysize N -- amount of rows the copy command will copy with each fetch
set long N -- size of your longest long
set copycommit M -- number of fetches to do before commit (N*M rows!!)

in plus before doing this. see the manual for all the options....

BUT i would recommend using blobs, lob datatypes since long and long raw are for backward compatibility.