Modify table datatype from VARCHAR2(4000) to CLOB

[ebrian]

If the VARCHAR2(4000) column is empty, then you can modify the column to LONG first and then to CLOB.

SQL> desc test_clob
 Name                                 Null?    Type
 ------------------------------------ -------- -----------------
 A1                                            NUMBER
 A2                                            VARCHAR2(4000)

SQL> alter table test_clob modify a2 long;

Table altered.

SQL> alter table test_clob modify a2 clob;

Table altered.

SQL> desc test_clob
 Name                                 Null?    Type
 ------------------------------------ -------- -----------------
 A1                                            NUMBER
 A2                                            CLOB

If there is data in the column, you can do the following:

SQL> create table test_clob (a1 number, a2 varchar2(4000));

Table created.

SQL> insert into test_clob values (1, rpad('*',4000,'*'));

1 row created.

SQL> alter table test_clob add (a2_clob clob);

Table altered.

SQL> update test_clob set a2_clob = a2;

1 row updated.

SQL> alter table test_clob drop (a2);

Table altered.

SQL> alter table test_clob add (a2 clob);

Table altered.

SQL> update test_clob set a2 = a2_clob;

1 row updated.

SQL> alter table test_clob drop (a2_clob);

Table altered.

SQL> desc test_clob
 Name                                 Null?    Type
 ------------------------------------ -------- --------------
 A1                                            NUMBER
 A2                                            CLOB