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.

Answer 1

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