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.

Solution

You may try this:

1. Add a new column as varchar2

   alter table my_table add (new_column varchar2(1000));

2. UPDATE CLOB name to varchar2 column;

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

After testing your data:

3. DROP CLOB column

   alter table my_table drop column old_column

4. Rename varchar2 column to CLOB column name

   alter table my_table rename column new_column to old_column

Comments

[PHPnoob]

lol i was thinking the same but I can't do that. They should be away to change clob datatype

[Rahul Tripathi]

They should be away to change clob datatype..What does that mean?

[PHPnoob]

it means changing clob to varachar2 or number same as you can change a varachar2(100) to varchar2(1000) or vaachar2 to number, etc

[Rahul Tripathi]

@PHPnoob:- Updated my answer. Does that help?

[PHPnoob]

you want to me add a new colum while I am trying to change the existing colum datatype from clob to varchar2

[Rahul Tripathi]

@PHPnoob:- I understand that but that would be the easiest one. Check this related thread: dba.stackexchange.com/questions/5948/…