When you try to convert datatype CLOB to VARCHAR2, you get the ORA-22859 invalid modification of columns error even if the column is empty.
In order to overcome this error. There is a basic solution. You can add a new column with intended datatype and update it. Then, rename the columns and drop the old column. Let’s demonstrate this solution with a simple example.
SQL> CREATE TABLE T_TEST_TAB
2 (
3 ID NUMBER,
4 DESCRIPTION VARCHAR2(20),
5 BIGCOLUMN CLOB
6 );
Tablo yarat²ld².
SQL> ALTER TABLE T_TEST_TAB MODIFY BIGCOLUMN VARCHAR2(4000);
ALTER TABLE T_TEST_TAB MODIFY BIGCOLUMN VARCHAR2(4000)
*
1 sat²r²nda HATA:
ORA-22859: geersiz s³tun dei■tirme
As can be seen from the above code snippet. ORA-22859 has occured.
--add new column with intended datatype
ALTER TABLE T_TEST_TAB ADD BIGCOLUMN_NEW VARCHAR2(4000);
--update the new column in case of it is not null
UPDATE T_TEST_TAB SET BIGCOLUMN_NEW = BIGCOLUMN;
--rename the column names
ALTER TABLE T_TEST_TAB RENAME COLUMN BIGCOLUMN TO BIGCOLUMN_OLD;
ALTER TABLE T_TEST_TAB RENAME COLUMN BIGCOLUMN_NEW TO BIGCOLUMN;
--old column can be dropped
ALTER TABLE T_TEST_TAB DROP COLUMN BIGCOLUMN_OLD;
DESC T_TEST_TAB;
TABLE T_TEST_TAB
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
DESCRIPTION VARCHAR2(20)
BIGCOLUMN VARCHAR2(4000)
I hope it’s useful.