Modify VARCHAR2 datatype to CLOB in OracleDB

by Denis Danov
June 12, 2014

frontpage, Oracle Database, Oracle Fusion Middleware
One Comment

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

1	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.

1	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);

1	ALTER TABLE REPORT ADD REPORT_DETAILS_COPY VARCHAR2(4000);

COMMIT;    -- Commit is here so we can reference the copy column later

1	COMMIT; -- Commit is here so we can reference the copy column later

UPDATE REPORT SET REPORT_DETAILS_COPY = REPORT_DETAILS;

1	UPDATE REPORT SET REPORT_DETAILS_COPY = REPORT_DETAILS;

COMMIT;

COMMIT;

UPDATE REPORT SET REPORT_DETAILS = NULL;

1	UPDATE REPORT SET REPORT_DETAILS = NULL;

COMMIT;

COMMIT;

ALTER TABLE REPORT MODIFY REPORT_DETAILSLONG;

1	ALTER TABLE REPORT MODIFY REPORT_DETAILSLONG;

ALTER TABLE REPORT MODIFY REPORT_DETAILSCLOB;

1	ALTER TABLE REPORT MODIFY REPORT_DETAILSCLOB;

UPDATE REPORT SET REPORT_DETAILS = REPORT_DETAILS_COPY ;

1	UPDATE REPORT SET REPORT_DETAILS = REPORT_DETAILS_COPY ;

COMMIT;

COMMIT;

ALTER TABLE REPORT DROP COLUMN REPORT_DETAILS_COPY ;

1	ALTER TABLE REPORT DROP COLUMN REPORT_DETAILS_COPY ;

ALTER INDEX REPORT_PK REBUILD;

1	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?

Denis Danov

Java EE and Oracle Developer at Dreamix

navigation

Modify VARCHAR2 datatype to CLOB in OracleDB

Denis Danov

Do you want more great blogs like this?

Subscribe for Dreamix Blog now!

IT Offshoring for Canadian Businesses: A Scale and Growth Strategy