How to change varchar column data type to CLOB

Today I will demonstrate how we can made changes on existing table column type.

Describe that we have a table and column varchar2 and data on that column is not null. So, what should we do? We should add column on table with CLOB type. Please look at below example.

Our table name is TEST_TBL and structure of table is:

create table ulfet.test_tbl
(
log_id number,
log_date date,
log_text varchar2(4000)
);

We want change log_text column type VARCHAR2 to CLOB type.

Add new column to TEST_TBL table.
SQL> alter table test_tbl add log_text2 clob;

Then update value of log_text to log_text2.

update TEST_TBL
set log_text2 = log_text;

Drop log_text column.
SQL> alter table test_tbl drop log_text;

Rename log_text2 to log_text.
SQL> alter table test_tbl rename column log_text2 to log_text;

That is all :)