ORA-00997: illegal use of LONG datatype: while creating a table with select statement

Question

ORA-00997: illegal use of LONG datatype

I have created a dblink in Oracle between two database Ora1 & Ora2.

A table tab1 in Ora1 has column of LONG datatype, so while creating a table tab2 in ora2 with the following sybntax:

create table Ora2.tab2 as select tab1 from Ora1.tab1@dblink_nm;

gives an error of "ORA-00997: illegal use of LONG datatype"

So I tried converting tab1 to LOB. with below syntax:

create table Ora2.tab2 as select TO_LOB(tab1) from Ora1.tab1@dblink_nm;

But still i am facing same issue "ORA-00997: illegal use of LONG datatype".

Is there any alternate to process the data and create a table?

can you create a view in the source schema, where you already do the cast to lob? https://stackoverflow.com/questions/29116396/workaround-for-ora-00997-illegal-use-of-long-datatype, https://stackoverflow.com/questions/29853945/illegal-use-of-long-datatype-oracle, — Cee McSharpface, Jun 30 '17 at 14:51
Did you mean to pass a table name to TO_LOB? Or is the name of your LONG column the same as the table? — Bob Jarvis - Слава Україні, Jun 30 '17 at 16:27
The real question is: why are you still using the `LONG` data type? That has been deprecated over 15 years ago. — , Jul 02 '17 at 16:50
@BobJarvis Kindly excuse, I meant it with specific column which has LONG datatype of table tab1 — Pooja, Jul 03 '17 at 12:43

score 0 · Accepted Answer · answered Jul 02 '17 at 16:45

There's always a way to move data over a database link but some data types require multiple steps.

One option is to do the LONG to LOB conversion on the remote database, copy the converted data over, and then cleanup the intermediate table:

begin
    dbms_utility.exec_ddl_statement@dblink_nm('create table temp_lob_results as select to_lob(a) a from tab1');
    execute immediate 'create table tab2 as select * from temp_lob_results@dblink_nm';
    dbms_utility.exec_ddl_statement@dblink_nm('drop table temp_lob_results');
end;
/

Another option is to use PL/SQL, which can implicitly convert LONG to LOBs. (Normally using PL/SQL like this is a horrible idea because it's so much slower and more complicated than a single SQL statement.)

--create table tab2(...);

begin
    for tab1_rows in
    (
        select * from tab1@dblink_nm
    ) loop
        insert into tab2 values(tab1_rows.a);
    end loop;
end;
/

I could get it done with second block. but finding it difficult for the 1st anonymous block. it throws an error for the 1st stmt while creating a table "dbms_utility.exec_ddl_statement@dblink_nm('create table temp_lob_results as select to_lob(a) a from tab1');" — Pooja, Jul 03 '17 at 12:48
@Pooja What is the error message? You might want to try to run the command directly on the remote database first to figure out the exact syntax, and then use that statement in the remote call. — Jon Heller, Jul 03 '17 at 14:07

ORA-00997: illegal use of LONG datatype: while creating a table with select statement

1 Answers1