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 :
|
1 |
ALTER TABLE table_name MODIFY column_name datatype |
If you try to use the altered code from above you will get the following error:
|
1 |
ORA-22858: invalid alteration of datatype. |
|
1 |
ALTER TABLE REPORT ADD REPORT_DETAILS_COPY VARCHAR2(4000); |
|
1 |
COMMIT; -- Commit is here so we can reference the copy column later |
|
1 |
UPDATE REPORT SET REPORT_DETAILS_COPY = REPORT_DETAILS; |
|
1 |
COMMIT; |
|
1 |
UPDATE REPORT SET REPORT_DETAILS = NULL; |
|
1 |
COMMIT; |
|
1 |
ALTER TABLE REPORT MODIFY REPORT_DETAILSLONG; |
|
1 |
ALTER TABLE REPORT MODIFY REPORT_DETAILSCLOB; |
|
1 |
UPDATE REPORT SET REPORT_DETAILS = REPORT_DETAILS_COPY ; |
|
1 |
COMMIT; |
|
1 |
ALTER TABLE REPORT DROP COLUMN REPORT_DETAILS_COPY ; |
|
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?
