ORA-22859 invalid modification of columns

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: geersiz s³tun de­i■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.

Posted in SQL
Leave a Reply

Your email address will not be published.