Need help on Oracle sql
Posted on 2014-10-11
I have table called my_Comments , which has an column called comments which is CLOB. I have to change the CLOB column to VARCHAR2 , Iam following the below approach to keep the column position , Below is the error Iam getting. Any help is really appreciated.
The table is stored in USER_TAB tablespace and the CLOB is stored in USER_LOB tablespace. As of now the column comments data is stored in USER_LOB tablespace. After changing it to VARCHAR2 , I need to move it from USER_LOB to USER_TAB tablespace.
MY_COMMENTS -- Stored in USER_TAB tablespace.
TITLE VARCHAR2(4000 CHAR),
COMMENTS CLOB --> Stored in USER_LOB tablespace.
alter table my_comments add c2 varchar2(4000);
update my_comments set c2 = DBMS_LOB.SUBSTR(comments,1,4000);
update my_comments set comments = null;
alter table my_comments modify COMMENTS LONG;
alter table t modify c clob;
update t set c = c2;
alter table t drop column c2;
Error starting at line 9 in command:
alter table my_comments modify COMMENTS LONG
SQL Error: ORA-22859: invalid modification of columns
22859. 00000 - "invalid modification of columns"
*Cause: An attempt was made to modify an object, REF, VARRAY, nested
table, or LOB column type.
*Action: Create a new column of the desired type and copy the current
column data to the new type using the appropriate type
Any help is really appreciated.