Modify VARCHAR2 datatype to CLOB in OracleDB

Modify VARCHAR2 datatype to CLOB in OracleDB

It is a common scenario where you do not design your database very well in the beginning of a project. Therefore you have to do some changes and alter tables.

One common place for changes is the datatype of your column. You may need to change either its precision or the whole type. In most cases it is easy just 1 line of code :

But that is not the case with changing VARCHAR2 to CLOB. The difference between the 2 types is that in VARCHAR2 you have to specify the length of the stored string and that length is limited to 4000 characters while CLOB can store up to 128 terabytes of character data in the database.

If you try to use the altered code from above you will get the following error:

So we will use the following script to accomplish the task. It creates copy of the desired column which stores the current values, changes the type of the column and then writes back the data from the copy column.

The last line is there because after the alternation of the table the index went in an unusable state.

After the rebuild, everything works as expected.


Was this helpful?

Do you have some other solutions that you can share? 

Denis Danov

Java EE and Oracle Developer at Dreamix

More Posts - Website

Follow Me:
TwitterLinkedInGoogle Plus

Do you want more great blogs like this?

Subscribe for Dreamix Blog now!