Home » SQL & PL/SQL » SQL & PL/SQL » Change Datatype to CLOB
Change Datatype to CLOB [message #274949] Wed, 17 October 2007 19:54 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Database link
Next Topic: errors in a package
Goto Forum:
  


Current Time: Tue Nov 05 13:54:40 CST 2024