Converting VARCHAR2 to CLOB and CLOB to VARCHAR2 for ORACLE 10g

1. Converting Varchar2 to Clob

ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  CLOB);

Add a column named "TEMP_DESCRIPTION_TEXT"to the table whose data type will be CLOB.

UPDATE TEST SET TEMP_DESCRIPTION_TEXT=DESCRIPTION_TEXT;
COMMIT;

Copy the text from existing column "DESCRIPTION_TEXT" to the new column "TEMP_DESCRIPTION_TEXT".

ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

Drop the old column named "DESCRIPTION_TEXT".

ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;

Rename the new column "TEMP_DESCRIPTION_TEXT" with old name "DESCRIPTION_TEXT".

2. Converting Clob to Varchar2


ALTER TABLE TEST ADD (TEMP_DESCRIPTION_TEXT  VARCHAR2(4000 BYTE));

Add a column named "TEMP_DESCRIPTION_TEXT"to the table whose data type will be VARCHAR2.


UPDATE TEST SET TEMP_DESCRIPTION_TEXT=DBMS_LOB.SUBSTR(DESCRIPTION_TEXT,4000,1);
COMMIT;

Copy the text from existing column "DESCRIPTION_TEXT" to the new column "TEMP_DESCRIPTION_TEXT". 

ALTER TABLE TEST DROP COLUMN DESCRIPTION_TEXT;

Drop the old column named "DESCRIPTION_TEXT".

ALTER TABLE TEST RENAME COLUMN TEMP_DESCRIPTION_TEXT TO DESCRIPTION_TEXT;

Rename the new column "TEMP_DESCRIPTION_TEXT" with old name "DESCRIPTION_TEXT".