Per Oracle Documentation “Oracle strongly recommends that you convert LONGRAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns “Reference: http://docs.oracle.com/cd/E16655_01/server.121/e17209/sql_elements001.htm#SQLRF50993

So if you have a legacy database that has data stored in LONG RAW you need to move it to BLOB or any other format still supported by Oracle.

In this document I am exploring the conversion from LONG RAW to BLOB and the different techniques for doing it.

To Find all schema’s that have tables with “LONG RAW” column, use the following query:

select * from DBA_TAB_COLUMNS where DATA_TYPE=’LONG RAW’;

selectdistinct(OWNER)from DBA_TAB_COLUMNS where DATA_TYPE=’LONG RAW’;

 

For illustration, let us first create a simple table with LONG RAW column it.

In this example, assume the schema name is “AIS” and the table name is “t1”.

 

CREATETABLE AIS.t1(idNUMBER, doc LONGRAW);

 

//after creation of the empty table you need to insert records

 

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Test to go into LONG RAW column’));

 

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Dear’));

 

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Dear2’));

 

INSERTINTO AIS.t1 VALUES(1, utl_raw.cast_to_raw(‘Hello Ya M3alem’));

 

Methodology I :  ALTER STATEMENT

This is before conversion:

 

After executing the sql command:

ALTERTABLE AIS.t1 MODIFY( doc BLOB);

Methodology II: TO_LOB Operator

 

Since the table with LONG RAW column exists, you need to create another table with the “blob” column data type, and insert the records from the original table.

CREATETABLE AIS.t2 (idNUMBER, doc2 BLOB);

INSERTINTO AISDBA.t2 SELECTid,TO_LOB(doc)FROM AISDBA.t1;

The Records were inserted to the second table successfully!!

After performing the insertion you need to “COMMIT” your transaction.

Then, you need to create a public synonym for the new table so applications can refrence this new table with the “BLOB” data type.

CREATE SYNONYM t2 FOR t1;

Methodology III: Data Pump

 

You can export the tables where there it has “LONG RAW” then drop the original ones in the database and re-create them with “BLOB” column data type. And then, try to re-import using impdp command.