copying a table with a long datatype 1

[ddrillich]

Good Day,

I'm trying to make backup copy of a table using the following statement:

create table vgn_users_06152005 as
select * from vgn_users

I get the following error message:
ORA00997: illegal use of LONG datatype

One of the columns is obviously of LONG datatype.

Any thoughts?

Thanks,
Dan

 

[SantaMufasa]

Dan,

This scenario shoud help you. It uses a SQL*Plus "COPY" command...this is not a SQL command, but as long as you are using SQL*Plus, it works fine and specifically supports copying of LONG columns:

SQL> desc dan
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER
 X                                LONG

SQL> select * from dan;

        ID X
---------- ------------
         1 Value one.

SQL> desc dan2
ERROR:
ORA-04043: object dan2 does not exist

SQL> create table dan2 as select * from dan;
create table dan2 as select * from dan
                            *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

SQL> copy from test/test@dhunt create dan2 (id, x) USING select * from dan
Table DAN2 created.

   1 rows selected from test@dhunt.
   1 rows inserted into DAN2.
   1 rows committed into DAN2 at DEFAULT HOST connection.

SQL> desc dan2
 Name                    Null?    Type
 ----------------------- -------- ----------------
 ID                               NUMBER(38)
 X                                LONG

SQL> select * from dan2;

        ID X
---------- --------------------
         1 Value one.

Let us know if this provides you with code that resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[ddrillich]

Thank you Dave!