How to Convert a VARCHAR2 Column to a CLOB Column(Doc ID 754542.1)

admin

v>Goal
LOBS or Large OBjects are used to store non-character data, such as mp3s, videos, pictures and
long character data. Binary large objects, or BLOBs, and character large objects, or CLOBs, can
store up to terabytes of data.
This Note will describe that how to convert a VARCHAR2 column to a CLOB column if needed.
Solution
Run the following script from SQL*Plus:
-- Create a test table
DROP TABLE varchar_to_clob;
CREATE TABLE varchar_to_clob (col1 NUMBER, col2 VARCHAR2(4000));
-- Populate the table with sample data
INSERT INTO varchar_to_clob (col1, col2) VALUES (1, 'This is a test item 1');
INSERT INTO varchar_to_clob (col1, col2) VALUES (2, 'This is a test item 2');
-- Validate the data has been inserted properly
SELECT * FROM varchar_to_clob;
-- Add a new CLOB column to the table
ALTER TABLE varchar_to_clob ADD col3 CLOB;
-- Verify the change
DESC varchar_to_clob
-- Perform the following update statement which converts and copies
-- the VARCHAR2 data into the newly created CLOB columnUPDATE varchar_to_clob SET col3 = TO_CLOB(col2);
-- Verify the change
SELECT * FROM varchar_to_clob;
-- Drop the VARCHAR2 column as it is no longer needed
ALTER TABLE varchar_to_clob DROP COLUMN col2;
-- Change the name of col3 to col2
ALTER TABLE varchar_to_clob RENAME COLUMN col3 TO col2;
-- Verify the change
DESC varchar_to_clob
-- Validate the date has been converted properly
SELECT * FROM varchar_to_clob;
Sample output from running the script:
SQL> -- Create a test table
SQL> DROP TABLE varchar_to_clob;
DROP TABLE varchar_to_clob
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE varchar_to_clob (col1 NUMBER, col2 VARCHAR2(4000));
Table created.
SQL>
SQL> -- Populate the table with sample data
SQL> INSERT INTO varchar_to_clob (col1, col2) VALUES (1, 'This is a test item 1');
1 row created.
SQL> INSERT INTO varchar_to_clob (col1, col2) VALUES (2, 'This is a test item 2');
1 row created.
SQL>
SQL> -- Validate the data has been inserted properly
SQL> SELECT * FROM varchar_to_clob;
COL1 COL2
---------- ------------------------
1 This is a test item 1
2 This is a test item 2SQL>
SQL> -- Add a new CLOB column to the table
SQL> ALTER TABLE varchar_to_clob ADD col3 CLOB;
Table altered.
SQL>
SQL> -- Verify the change
SQL> DESC varchar_to_clob
Name Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 VARCHAR2(4000)
COL3 CLOB
SQL>
SQL> -- Perform the following update statement which converts and copies
SQL> -- the VARCHAR2 data into the newly created CLOB column
SQL> UPDATE varchar_to_clob SET col3 = TO_CLOB(col2);
2 rows updated.
SQL>
SQL> -- Verify the change
SQL> SELECT * FROM varchar_to_clob;
COL1 COL2 COL3
---------- ------------------------ ------------------------
1 This is a test item 1 This is a test item 1
2 This is a test item 2 This is a test item 2
SQL>
SQL> -- Drop the VARCHAR2 column as it is no longer needed
SQL> ALTER TABLE varchar_to_clob DROP COLUMN col2;
Table altered.
SQL>
SQL> -- Change the name of col3 to col2
SQL> ALTER TABLE varchar_to_clob RENAME COLUMN col3 TO col2;
Table altered.
SQL>
SQL> -- Verify the change
SQL> DESC varchar_to_clobName Null? Type
----------------------------------------- -------- ----------------------------
COL1 NUMBER
COL2 CLOB
SQL>
SQL> -- Validate the date has been converted properly
SQL> SELECT * FROM varchar_to_clob;
COL1 COL2
---------- ------------------------
1 This is a test item 1
2 This is a test item 2
SQL>