LONG to BLOB Migration

In release 8.1, a new SQL function, TO_LOB, copies data from a LONG column in a table to a LOB column. The datatype of the LONG and LOB must correspond for a successful copy. For example, LONG RAW data must be copied to BLOB data, and LONG data must be copied to CLOB data. In the next example we show how to migrate a table with one LONG to a CLOB datatype.

Create the LOB Tablespace

CREATE TABLESPACE lob1
DATAFILE '/lh4/lob1.dbf' SIZE 2048064K REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 50M
PERMANENT
ONLINE;

Disable temporarily all Foreign Keys

set feed off;
spool gen_dis_cons.sql;
SELECT 'ALTER TABLE ' table_name
' DISABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/
spool off;
set feed on;
@gen_dis_cons.sql;

Convert LONG to LOB in temporary Table

Create a temporary table with converted BLOB field.

CREATE TABLE lob_tmp
TABLESPACE tab
AS SELECT id, TO_LOB(bdata) bdata FROM document;

Drop and Rename Tables

DROP TABLE document;
RENAME lob_tmp TO document;

Create the necessary Constraints and enable the Foreign Keys again

set feed off;
set heading off;
spool gen_ena_cons.sql;
SELECT 'ALTER TABLE ' table_name
' ENABLE CONSTRAINT ' constraint_name ';'
FROM user_constraints WHERE UPPER(constraint_name) like 'FK_%'
/

spool off;
set feed on;
@gen_ena_cons.sql;

Courtesy: akadia