Today We will show 2 methods how convert long column to clob.
METHOD I: Simple method using TO_LOB procedure:
CREATE TABLE NBA_LONG (ID NUMBER, TEXT_L LONG);
CREATE TABLE NBA_CLOB (ID NUMBER, TEXT_C CLOB);
INSERT INTO NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
INSERT INTO NBA_CLOB SELECT ID, TO_LOB(TEXT_L) FROM NBA_LONG;
COMMIT;
As you see in table NBA_CLOB column text_c contain information of column text_l from table NBA_LONG.
----
METHOD II: With using DBMS_REDEFINITION package
CREATE TABLE SCOTT.NBA_LONG (ID NUMBER PRIMARY KEY, TEXT_L LONG);
CREATE TABLE SCOTT.NBA_CLOB (ID NUMBER, TEXT_C CLOB);
INSERT INTO SCOTT.NBA_LONG VALUES (1, 'LONG SHOULD BE CONVERTED TO CLOB');
COMMIT;
DECLARE
VM BINARY_INTEGER;
BEGIN
--VERIFY THAT THE TABLE NBA_LONG IS A CANDIDATE FOR ON LINE REDEFINITION
DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT','NBA_LONG');
--START THE REDEFINITION PROCESS
DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT','NBA_LONG','NBA_CLOB','ID ID,TO_LOB(TEXT_L) TEXT_C', DBMS_REDEFINITION.CONS_USE_PK);
--AUTOMATICALLY CREATE ANY TRIGGERS, INDEXES AND CONSTRAINTS ON NBA_CLOB
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCOTT','NBA_LONG','NBA_CLOB', DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, FALSE, VM);
--OPTIONALLY FOR SYNCHRONIZE
DBMS_REDEFINITION.SYNC_INTERIM_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
-- COMPLETE THE REDEFINITION
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'NBA_LONG','NBA_CLOB');
END;