Sunday, September 8, 2013

ORA-22859: invalid modification of columns

Today while Doing SqlServer Migration to Oracle db. i came to know below issue ,

As part of  requirement  apps team want to change joshiv_TEXT columan data type from   NOT N ULL CLOB to VARCHAR2(1000).

ALTER joshiv MODIFY  joshiv_TEXT varchar2(1000);

ALTER TABLE joshiv MODIFY  joshiv_TEXT varchar2(1000)
                                                         *
ERROR at line 1:
ORA-22859: invalid modification of columns

eugetp12 >desc joshiv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 joshiv_STMT                             NOT NULL VARCHAR2(10 CHAR)
 joshiv_KEY                              NOT NULL VARCHAR2(9 CHAR)
 joshiv_TYPE                                      VARCHAR2(10 CHAR)
 joshiv_TEXT                             NOT NULL CLOB


ALTER TABLE joshiv ADD joshiv_TEXT_TEMP VARCHAR2(1000);

Table altered.

UPDATE joshiv  SET FOOTNOTE_TEXT_temp = DBMS_LOB.SUBSTR (joshiv_TEXT, 1000)
37 rows updated.

ALTER TABLE joshiv DROP COLUMN joshiv_TEXT;
Table altered.
ALTER TABLE joshiv RENAME COLUMN FOOTNOTE_TEXT_TEMP TO joshiv_TEXT;
Table altered.

desc joshiv
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 joshiv_STMT                             NOT NULL VARCHAR2(10 CHAR)
 joshiv_KEY                              NOT NULL VARCHAR2(9 CHAR)
 joshiv_TYPE                                      VARCHAR2(10 CHAR)
 joshiv_TEXT                                      VARCHAR2(1000)

1 comment: