How to change a dataype CLOB TO VARCHAR2(sql)

Question

Table: customers

ID      NAME             DATATYPE
NUMBER  VARCHAR2(100)    CLOB

I want to change the DATA column from CLOB to `VARCHAR2(1000)

I have try ALTER TABLE customers MODIFY DATA VARCHAR2 (1000) also

ALTER TABLE customers MODIFY (DATA VARCHAR2 (1000))

also

alter table customers  modify
(data VARCHAR2(4000))

those normally works if the datatype is not a clob but I am getting a ORA-22859 because I am using oracle toad/apex.

score 25 · Accepted Answer · edited Jun 20 '20 at 09:12

25

You may try this:

Add a new column as varchar2

alter table my_table add (new_column varchar2(1000));
UPDATE CLOB name to varchar2 column;

update my_table set new_column=dbms_lob.substr(old_column,1000,1);

After testing your data:

DROP CLOB column

alter table my_table drop column old_column
Rename varchar2 column to CLOB column name

alter table my_table rename column new_column to old_column

edited Jun 20 '20 at 09:12

Community

answered Nov 07 '13 at 17:01

Rahul Tripathi

lol i was thinking the same but I can't do that. They should be away to change `clob` datatype – PHPnoob Nov 07 '13 at 17:04
`They should be away to change clob datatype`..What does that mean? – Rahul Tripathi Nov 07 '13 at 17:05
it means changing clob to varachar2 or number same as you can change a varachar2(100) to varchar2(1000) or vaachar2 to number, etc – PHPnoob Nov 07 '13 at 17:08
1

you want to me `add` a new colum while I am trying to change the existing colum `datatype` from clob to `varchar2` – PHPnoob Nov 07 '13 at 17:12
@PHPnoob:- I understand that but that would be the easiest one. Check this related thread: http://dba.stackexchange.com/questions/5948/how-to-replace-a-clob-column-by-a-varchar24000-column – Rahul Tripathi Nov 07 '13 at 17:13

1 Answers1