Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 799
PHPnoob Avatar asked Nov 07 '13 16:11

PHPnoob


People also ask

Can we convert CLOB to string?

To convert CLOB data type to string Reader r = clob. getCharacterStream(); Read each character one by one from the retrieved Stream of characters and append them to the StringBuilder or StringBuffer.

What is the difference between VARCHAR2 and CLOB?

The difference between the 2 types is that in VARCHAR2 you have to specify the length of the stored string and that length is limited to 4000 characters while CLOB can store up to 128 terabytes of character data in the database.

Can we change VARCHAR2 to CLOB in Oracle?

just create new table as select from the old (using to_lob on the column) and then index, constrain, etc the new table, then drop old, rename new to old.


1 Answers

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

like image 63
Rahul Tripathi Avatar answered Oct 18 '22 16:10

Rahul Tripathi