Convert datatype Clob to Varchar2 Oracle

Question

I have an Oracle table with a column of type clob. I want to preserve the column order and change the datatype to a varchar2. The column just contains text.

update IN_MSG_BOARD set MSG_TEXT = null;
alter table IN_MSG_BOARD modify MSG_TEXT long;
alter table IN_MSG_BOARD modify MSG_TEXT varchar2(4000);

I'm getting the standard message:

ORA-22859: invalid modification of columns

I have tried making the column null and then converting to char or long, then to varchar2. But nothing seems to be working. I would prefer to not have to copy the table to change the one column.

I don't just want to read the contents. I want to change the datatype of the column from clob to varchar2.

Help would be greatly appreciated. I have been working on this for a while. Let me know if you have any questions.

Answer 1

You can do next steps :

1. alter table my_table add (new_column varchar2(4000));

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

3. alter table my_table drop column old_column