Oracle: Changing VARCHAR2 column to CLOB
The VARCHAR2 column cannot be directly converted to CLOB but it can be done in 2 steps:
- Convert column datatype from VARCHAR2 to LONG.
- Convert column datatype from LONG to CLOB.
ALTER TABLE table MODIFY column long;
ALTER TABLE table MODIFY column clob;
For Oracle 11g:
ALTER TABLE table MODIFY column long;
ALTER TABLE table MODIFY column clob;
The most straightforward way, given that the operation of moving from a varchar
column to a CLOB
is disallowed, would be to create a new column and move the data from the old column to the new column:
ALTER TABLE some_table ADD (foo CLOB);
UPDATE some_table SET foo = old_column;
ALTER TABLE some_table DROP COLUMN old_column;
ALTER TABLE some_table RENAME COLUMN foo TO old_column;