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 :
ALTER TABLE table_name MODIFY column_name datatype
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:
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.
ALTER TABLE REPORT ADD REPORT_DETAILS_COPY VARCHAR2(4000);
COMMIT; -- Commit is here so we can reference the copy column later
UPDATE REPORT SET REPORT_DETAILS_COPY = REPORT_DETAILS;
COMMIT;
UPDATE REPORT SET REPORT_DETAILS = NULL;
COMMIT;
ALTER TABLE REPORT MODIFY REPORT_DETAILSLONG;
ALTER TABLE REPORT MODIFY REPORT_DETAILSCLOB;
UPDATE REPORT SET REPORT_DETAILS = REPORT_DETAILS_COPY ;
COMMIT;
ALTER TABLE REPORT DROP COLUMN REPORT_DETAILS_COPY ;
ALTER INDEX REPORT_PK REBUILD;
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? 
