I am running Oracle 8.0.6 EE. I have a table with a long raw column. I have created a new table replacing the longraw column with a blob column. I would like to migrate my data into the new table. I have tried insert into new_table select ..,to_lob(longrawcolumn) from old_table. This command fails with an error. Any ideas how I can succesfully go round this problem.
Here is the comand as well as the error
SQL> insert into test2 select * from test1;
insert into test2 select * from test1
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
SQL> insert into test2 select id, to_blob(img) from test1;
insert into test2 select id, to_blob(img) from test1
ERROR at line 1:
ORA-00904: invalid column name
Good luck on this!! :-) If you're good at PL/SQL, you may have better luck attempting to convert it within a procedure, I know at my last work they did this with LONG to CLOB conversion, but it took em awhile to figure out. You may have some luck surfing the Oracle tech sites for a solution script or procedure.
I just garnered this from MetaLink. Hope this helps:
Oracle8i provides a TO_LOB function that allows for the conversion of LONG columns to LOB columns. Prior to Oracle8, you must perform the conversion using either PL/SQL or a program using the OCI interface.
Information on both capabilities is available in the MetaLink Repository. Enter 'convert long raw blob' (without the quotes) and search. The following articles will be returned in the result set:
Doc ID: 1071540.6 CAN A LONG RAW DATATYPE BE CONVERTED TO A CLOB DATATYPE IN ORACLE 8.1.X?
Doc ID: 1012454.7 How to Convert from Long Raw to BLOB using PL/SQL