Sponsored Post Learn from the experts: Create a successful blog with our brand new courseThe WordPress.com Blog

WordPress.com is excited to announce our newest offering: a course just for beginning bloggers where you’ll learn everything you need to know about blogging from the most trusted experts in the industry. We have helped millions of blogs get up and running, we know what works, and we want you to to know everything we know. This course provides all the fundamental skills and inspiration you need to get your blog started, an interactive community forum, and content updated annually.

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>