Change Datatype to CLOB [message #274949] |
Wed, 17 October 2007 19:54 |
faiz_hyd
Messages: 294 Registered: February 2006
|
Senior Member |
|
|
Hi,
I was trying to change datatype of a column from Varchhar2 to CLOB and i got this
SQL> alter table lockbox_instruction modify ( instruction_tx Clob);
alter table lockbox_instruction modify ( instruction_tx Clob)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
Does that mean we can't change the datatype to CLOB,
Well I have the alternate, but was wondering is this the right way or am i missing some thing in my 1 Statement
alter table LOCKBOX_INSTRUCTION add INSTRUCTION_TX1 clob;
update LOCKBOX_INSTRUCTION set INSTRUCTION_TX1=INSTRUCTION_TX;
commit;
alter table drop column INSTRUCTION_TX;
alter table LOCKBOX_INSTRUCTION rename column INSTRUCTION_TX1 to INSTRUCTION_TX;
Thanks
|
|
|
Re: Change Datatype to CLOB [message #274950 is a reply to message #274949] |
Wed, 17 October 2007 20:48 |
ora110
Messages: 42 Registered: September 2007 Location: China
|
Member |
|
|
Yes,that means you can't change the datatype to clob.
you should do it follow the "action" below.
Quote: | Cause
An attempt was made to modify the column type to object, REF, nested table, VARRAY or LOB type.
Action
Create a new column of the desired type and copy the current column data to the new type using the appropriate type constructor.
|
FYI
[Updated on: Wed, 17 October 2007 20:50] Report message to a moderator
|
|
|
Re: Change Datatype to CLOB [message #274995 is a reply to message #274949] |
Thu, 18 October 2007 01:18 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> create table t (col varchar2(100));
Table created.
SQL> desc t
Name Null? Type
-------------------------------- -------- ----------------------
COL VARCHAR2(100 CHAR)
SQL> insert into t values ('my data');
1 row created.
SQL> select * from t;
COL
-----------------------------------------------------------------
my data
1 row selected.
SQL> alter table t add (col2 clob);
Table altered.
SQL> update t set col2=col;
1 row updated.
SQL> alter table t drop (col);
Table altered.
SQL> alter table t rename column col2 to col;
Table altered.
SQL> desc t
Name Null? Type
-------------------------------- -------- ----------------------
COL CLOB
SQL> select * from t;
COL
-----------------------------------------------------------------
my data
1 row selected.
Regards
Michel
|
|
|