PHPnoob PHPnoob - 23 days ago 5
SQL Question

How to change a dataype CLOB TO VARCHAR2(sql)

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

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:

  1. DROP CLOB column

    alter table my_table drop column old_column

  2. Rename varchar2 column to CLOB column name

    alter table my_table rename column new_column to old_column