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 drop CLOB column, add the varchar2 column, and then 'fix' the column order using online table redefinition; assuming you're on a version that supports that and you have permission to use the DBMS_REDEFINITION package. Very simple demo for a table with a primary key:

create table in_msg_board(col1 number primary key, msg_text clob, col3 date);

If you don't want to keep the data in your original column:

alter table IN_MSG_BOARD drop column msg_text;
alter table IN_MSG_BOARD add msg_text varchar2(4000);

If you do want to keep the data it's only two extra steps, shown in the rowid version below.

Create a redefinition table, with the columns in the order you want:

create table in_msg_board_redef(col1 number, msg_text varchar2(4000), col3 date);

And call the package:

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => user,
    orig_table => 'IN_MSG_BOARD',
    int_table => 'IN_MSG_BOARD_REDEF',
    col_mapping => 'col1 col1, msg_text msg_text, col3 col3');
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => user,
    orig_table => 'IN_MSG_BOARD',
    int_table => 'IN_MSG_BOARD_REDEF');
END;
/

desc in_msg_board;

Name     Null Type           
-------- ---- -------------- 
COL1          NUMBER         
MSG_TEXT      VARCHAR2(4000) 
COL3          DATE           

There is more in the documentation about checking the table is valid for redefinition, handling dependent tables (foreign keys) etc.

---

If your table doesn't have a primary key then you can use rowids by passing an additional option flag. For this demo I'll preserve the data too.

create table in_msg_board(col1 number, msg_text clob, col3 date);
insert into in_msg_board values (1, 'This is a test', sysdate);
alter table IN_MSG_BOARD add msg_text_new varchar2(4000);
update IN_MSG_BOARD set msg_text_new = dbms_lob.substr(msg_text, 4000, 1);
alter table IN_MSG_BOARD drop column msg_text;
alter table IN_MSG_BOARD rename column msg_text_new to msg_text;

desc in_msg_board

Name     Null Type           
-------- ---- -------------- 
COL1          NUMBER         
COL3          DATE           
MSG_TEXT      VARCHAR2(4000) 

So as before, at this point the new column (containing data this time) is there but in the wrong position. So redefine as before, but with the DBMS_REDEFINITION.CONS_USE_ROWID flag:

create table in_msg_board_redef(col1 number, msg_text varchar2(4000), col3 date);

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => user,
    orig_table => 'IN_MSG_BOARD',
    int_table => 'IN_MSG_BOARD_REDEF',
    col_mapping => 'col1 col1, msg_text msg_text, col3 col3',
    options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
  DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => user,
    orig_table => 'IN_MSG_BOARD',
    int_table => 'IN_MSG_BOARD_REDEF');
END;
/

desc in_msg_board;

Name     Null Type           
-------- ---- -------------- 
COL1          NUMBER         
MSG_TEXT      VARCHAR2(4000) 
COL3          DATE           

And the data is there too:

select * from in_msg_board;

      COL1 MSG_TEXT             COL3    
---------- -------------------- ---------
         1 This is a test       27-MAY-15

And as mentioned in the linked documentation, you can then drop the hidden column used to manage the rowids:

alter table in_msg_board drop unused columns;