Oracle BLOB – How to Copy BLOB, LONG RAW, and BFILE Columns Between Tables

bloboracle

I have a table name mytable in Oracle 9i.

columns of mytable are

id number(9,0)
C1 LONG RAW,
C2 VARCHAR2(256),
C3 BLOB,
C4 BFILE

when I try to do this:

create this_other_table as
select * from mytable;

I get this:

ORA-00997: illegal use of LONG datatype

I guess the rest of columns that are of some kind of binary data (BLOB, AND BFILE, besides LONG RAW) will yield errors also.

Would someone please shed some light on how to do this?

Best Answer

What Oracle says about your error?

You try to select distinct values from a long column, create a table using as select with a long column, insert into a table by selecting the long column from another table and an ORA-00997: illegal use of LONG datatype ... occurs

It is a restriction of the long columns that they cannot be used with a DISTINCT operator and cannot be referenced when creating a table with query (CREATE TABLE...AS SELECT...) or when inserting into a table(or view) with a query (INSERT INTO ... SELECT...)

And what Oracle recommends?

Oracle strongly recommends that you convert LONG RAW columns to binary LOB(BLOB) columns.

Here is how simulated your problem and prepared the solution

 SQL> create table mytable(id number (9,0), c1 long raw, c2 varchar2(256), c3 blob, c4 bfile);

Table created.

SQL> desc mytable
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                         NUMBER(9)
 C1                         LONG RAW
 C2                         VARCHAR2(256)
 C3                         BLOB
 C4                         BINARY FILE LOB

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


SQL> create table this_other_table as select to_lob(c1) c1, c2,c3,c4 from mytable;

Table created.

SQL>
Related Question