Migrating from long raw to blob
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Migrating from long raw to blob

  1. #1
    Join Date
    Sep 2000
    Posts
    33
    Hi

    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

  2. #2
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    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.
    Tim Hussar

  3. #3
    Join Date
    Oct 2001
    Location
    Buffalo, NY
    Posts
    46
    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

    Tim Hussar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width