Changing a column from VARCHAR2 to CLOB

CLOB  : Store large ANSI text.

SQL> create table test ( x int, y varchar2(4000) );

Table created.

SQL> insert into test values ( 1, ‘hello’ );

1 row created.
SQL> insert into test values ( 2, ‘hi’ );

1 row created.

SQL> insert into test values ( 3, ‘bye’ );

1 row created.

SQL> select * from test;

X Y
—– ———-
1 hello
2 hi
3 bye

SQL>

 

SQL> alter table test modify( y clob );
alter table test modify( y clob )
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype.

 

SQL> alter table test add ( temp clob );

Table altered.

SQL> update t set temp=y, y=null;

3 rows updated.
SQL> select * from t;

X Y TEMP
—– ———- ——————————————————————————–
1 hello
2 hi
3 bye

SQL>
SQL>
SQL>
SQL> alter table t drop column y;

Table altered.

SQL> select * from t;

X TEMP
—– ——————————————————————————–
1 hello
2 hi
3 bye

SQL>
SQL>
SQL> alter table t rename column temp to y;

Table altered.

SQL>
SQL> select * from t;

X Y
—– ———-
1 hello
2 hi
3 bye

SQL>

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s