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.
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.
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.
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.
You may try this:
Add a new column as varchar2
alter table my_table add (new_column varchar2(1000));
UPDATE CLOB name to varchar2 column;
update my_table set new_column=dbms_lob.substr(old_column,1000,1);
After testing your data:
DROP CLOB column
alter table my_table drop column old_column
Rename varchar2 column to CLOB column name
alter table my_table rename column new_column to old_column
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With