Oracle. How to copy BLOB, LONG RAW and BFILE column between tables 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?
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&gt; create table mytable(id number (9,0), c1 long raw, c2 varchar2(256), c3 blob, c4 bfile);

Table created.

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

SQL&gt; 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&gt; create table this_other_table as select to_lob(c1) c1, c2,c3,c4 from mytable;

Table created.

SQL&gt;