oracle alter clob,alter varchar2 column to clob

Hi,

You can cant modify from VARCHAR2 to CLOB but you can achieve your result like this:

1. Add a new column as CLOB

2. UPDATE varchar date to CLOB column;

3. DROP VARCHAR column

4. Rename CLOB column to VARCHAR column name

SQL>CREATE TABLE t ( name VARCHAR2(20), age number(3));

Table created.

SQL>INSERT INTO t VALUES('aaa',20);

1 row created.

SQL>INSERT INTO t VALUES('bbb',30);

1 row created.

SQL>COMMIT;

Commit complete.

SQL>ALTER TABLE t MODIFY name CLOB;

ALTER TABLE t MODIFY name CLOB

*

ERROR at line 1:

ORA-22858: invalid alteration of datatype

SQL>ALTER TABLE t ADD tmp_name CLOB;

Table altered.

SQL>UPDATE t SET tmp_name=name;

2 rows updated.

SQL>

SQL>ALTER TABLE t DROP COLUMN name;

Table altered.

SQL>

SQL>ALTER TABLE t RENAME COLUMN tmp_name to name;

Table altered.

SQL>

SQL>desc T;

Name Null? Type

----------------------------------------------------- -------- ------------------------------------

AGE NUMBER(3)

NAME CLOB

SQL>SELECT * FROM v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

PL/SQL Release 9.2.0.6.0 - Production

CORE 9.2.0.6.0 Production

TNS for Solaris: Version 9.2.0.6.0 - Production

NLSRTL Version 9.2.0.6.0 - Production

5 rows selected.

SQL>Regards