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.
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.
ORA-22858: invalid alteration of datatype.
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.
Do you have some other solutions that you can share?