How to Convert Long data type to Lob

It is relatively easy if you want to convert long datatype to lob data type.
This section describes the following techniques for migrating existing tables from LONG to LOB datatypes:

A)Using ALTER TABLE to Convert LONG Columns to LOB Columns

B)Copying a LONG to a LOB Column Using the TO_LOB Operator

C)Online Redefinition of Tables with LONG Columns where high availability is critical

D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility

With an example I have demonstrate these procedures.
A)Using ALTER TABLE to Convert LONG Columns to LOB Columns
----------------------------------------------------------------------

SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (2,'This is the second entered row');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> ALTER TABLE TEST_LONG_LOB MODIFY B CLOB;
Table altered.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB


B)Copying a LONG to a LOB Column Using the TO_LOB Operator
---------------------------------------------------------------------
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> COMMIT;
Commit complete.

SQL> CREATE TABLE TEST_CLOB AS SELECT A, TO_LOB(B) B FROM TEST_LONG_LOB;
Table created.

After you ensure that the data is accurately copied, you can drop the original table and create a view or synonym for the new table using one of the following sequences:

SQL> DROP TABLE TEST_LONG_LOB;
Table dropped.

SQL> CREATE VIEW TEST_LONG_LOB AS SELECT * FROM TEST_CLOB;
View created.

or

SQL> DROP TABLE TEST_LONG_LOB;
Table dropped.

SQL> CREATE SYNONYM TEST_LONG_LOB FOR TEST_CLOB;
Synonym created.

or rename the table
SQL> RENAME TEST_CLOB TO TEST_LONG_LOB;

Table renamed.

SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B CLOB

C)Online Redefinition of Tables with LONG Columns where high availability is critical
---------------------------------------------------------------------------------------------
1)This is the table that need to change LONG data.
SQL> CREATE TABLE TEST_LONG_LOB(A NUMBER PRIMARY KEY, B LONG);
Table created.

SQL> INSERT INTO TEST_LONG_LOB VALUES (1,'This is the first entered row');
1 row created.

SQL> COMMIT;
Commit complete.

2) Determine if the table is a candidate for online re-organization
SQL> exec dbms_redefinition.can_redef_table('ARJU','TEST_LONG_LOB');
PL/SQL procedure successfully completed.
A primary key is mandatory since materialized views and logs are created during the start of redefinition.

3)Create an Interim Table.

SQL> CREATE TABLE TEST_LONG_LOB_INT(A NUMBER NOT NULL, B CLOB);
Table created.

Note that interim table has no primary key.

4)Start the re-organization process
SQL>declare
col_mapping varchar2(1000);
BEGIN
-- map all the columns in the interim table to the original table
col_mapping :=
'a a , '||
'to_lob(b) b';

dbms_redefinition.start_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT', col_mapping);
END;
/
PL/SQL procedure successfully completed.
Here Arju is username.

5)Run dbms_redefinition.copy_table_dependents
SQL>declare
error_count pls_integer := 0;
BEGIN
dbms_redefinition.copy_table_dependents('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT',
1, true, true, true, false,
error_count);

dbms_output.put_line('errors := ' || to_char(error_count));
END;
/
PL/SQL procedure successfully completed.
6)Execute dbms_redefinition.finish_redef_table procedure.

exec dbms_redefinition.finish_redef_table('ARJU', 'TEST_LONG_LOB', 'TEST_LONG_LOB_INT');
PL/SQL procedure successfully completed.

SQL> DROP TABLE TEST_LONG_LOB_INT;
Table dropped.


SQL> DESC TEST_LONG_LOB;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B CLOB


D)Using Oracle Data Pump to Migrate a Database when you can convert using this utility
------------------------------------------------------------------------------------------
If you are exporting data as part of a migration to a new database, create a table on the destination database with LOB columns and Data Pump will call the LONG-to-LOB function implicitly.
Serach within my blog about data pump export or import.

Related Documents

Large Object (LOB) Datatypes with Example.

Advantages of LOB against LONG- Feature of LOB datatype

How to move LOB data to another tablespace

How to Insert Blob data(image, video) into oracle BLOB size

Char, Varchar2, Long etc Datatype Limits in Oracle