Steps to Convert the CLOB to VARCHAR2 Data Type

Steps to convert the CLOB(Character Large OBject) to Varchar2 data type.

Step 1: Create a new temporary column with varchar2
Step 2: Move the CLOB column content to new temporary column, if it is needed. (Note : By this situation you may loose the data depending upon Max length)
Step 3: Drop the CLOB column.
Step 4: Rename the temporary column with dropped CLOB column.

Example:

Step 1: ALTER TABLE your_table (temp_column VARCHAR(4000));
Step 2: UPDATE your_table SET temp_column = DBMS_LOB.SUBSTR(clob_column, 4000, 1);
Step 3: ALTER TABLE your_table DROP COLUMN clob_column;
Step 4: ALTER TABLE your_table COLUMN temp_column TO clob_column;

How to Convert the Data Type VARCHAR2 to CLOB in Oracle

We cannot directly convert the data type VARCHAR2 to CLOB(Character Large OBject). Using below two options, we can able to covert it.

Option 1:

Step 1 : Convert the VARCHAR2 To LONG Data Type
Step 2 : Convert the LONG to CLOB Data Type.

Example:

Step 1: ALTER TABLE YOUR_TABLE_NAME MODIFY(Your_Column_Name LONG);
Step 2: ALTER TABLE YOUR_TABLE_NAME MODIFY(Your_Column_Name CLOB;

Option 2:

Step 1: Add a new column as CLOB
Step 2: UPDATE VARCHAR2 date to CLOB column
Step 3: DROP VARCHAR column
Step 4: Rename CLOB column to VARCHAR column name