If the VARCHAR2(4000) column is empty, then you can modify the column to LONG first and then to CLOB.
Code:
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:
Code:
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