Change a existing column type (varchar to clob) usig SQL query .

Problem : Change a column type (varchar to clob) usig SQL query .
 
Solution : To change column type column should be dropped and then craeted again .. then if it contains data then risk to loose that .
To prevent it we can use following way : 
1.  Add a temp column :
ALTER TABLE TEST ADD temp VARCHAR2(4000);
2. copy data to temp column :
UPDATE TEST
   SET temp = DBMS_LOB.SUBSTR (a, 4000),
       a = NULL;
3. drop the original column "A"
ALTER TABLE TEST DROP COLUMN A;

4. Either rename the column "temp" to original "A"  ((if just modifying size)
ALTER TABLE TEST RENAME COLUMN temp TO A;

Optional  step
5. create column "A" as clob and repeat step 2 to copy data from "temp" to "A" and after copy remove "temp"

cheers

Kapil