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)
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)
FOOTNOTE_TEXT_temp = joshiv_TEXT_TEMP ???????
ReplyDelete