How to change dataype CLOB TO VARCHAR2 (sql)

Question

Table: Clients

ID NAME DATATYPE NUMBER VARCHAR2(100) CLOB 

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

I tried ALTER TABLE customers MODIFY DATA VARCHAR2 (1000) also

ALTER TABLE customers MODIFY (DATA VARCHAR2 (1000))

also

 alter table customers modify (data VARCHAR2(4000)) 

usually work if the data type is not a clob, but I get ORA-22859 because I use oracle toad / apex.

Answer 1

You can try the following:

• Add new column as varchar2

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

• UPDATE CLOB name in varchar2 column;

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

After testing your data :

3. Column DROP CLOB

   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