Skip to Main Content
  • Questions
  • Changing a column from VARCHAR2 to CLOB

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, chandini.

Asked: June 09, 2009 - 5:46 am UTC

Answered by: Tom Kyte - Last updated: September 23, 2013 - 5:32 pm UTC

Category: Database - Version: 10g

Viewed 100K+ times! This question is

You Asked

Hi Tom,
Apologies if this has been answered elsewhere. I did do a search but didn't find any relevant information.

I have a table that has been defined with a column of type VARCHAR2(4000). We are now looking to change this to a CLOB b'cos we need to store more data in it. I tried a straight forward ALTER TABLE and ended up with an error

SQL> alter table test
2 modify (value clob);
modify (value clob)
*
ERROR at line 2:
ORA-22858: invalid alteration of datatype


The SQL ref doesn't mention anything about converting VARCHAR2 into CLOB. How would I then do this?

Also, if I have stored procedures that use this column (insert/update/delete/query) how would changing it to CLOB affect these? Would they need a re-write to work with dbms_lob instead?

Thank you

(P.S:- Cannot believe I actually got a chance to Submit a question to you. Woohooo!)

and we said...

Longs can be converted to clobs

http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_long_lob.htm#sthref873

(but it requires a copy of the table be made under the covers)


Note:
The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements.



Your choices in this case will be:


a) new column

ops$tkyte%ORA10GR2> create table t ( x int, y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, 'hello' );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t modify( y clob );
alter table t modify( y clob )
                      *
ERROR at line 1:
ORA-22858: invalid alteration of datatype


ops$tkyte%ORA10GR2> !oerr ora 22858
22858, 00000, "invalid alteration of datatype"
// *Cause:   An attempt was made to modify the column type to object, REF,
//           nested table, VARRAY or LOB type.
// *Action:  Create a new column of the desired type and copy the current
//           column data to the new type using the appropriate type
//           constructor.

ops$tkyte%ORA10GR2> alter table t add ( temp clob );

Table altered.

ops$tkyte%ORA10GR2> update t set temp=y, y=null;

1 row updated.

ops$tkyte%ORA10GR2> alter table t drop column y;

Table altered.

ops$tkyte%ORA10GR2> alter table t rename column temp to y;

Table altered.

ops$tkyte%ORA10GR2> desc t
 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                                 NUMBER(38)
 Y                                                 CLOB

ops$tkyte%ORA10GR2> select * from t;

         X
----------
Y
-------------------------------------------------------------------------------
         1
hello


b) online redefine

ops$tkyte%ORA10GR2> create table t ( x int primary key, y varchar2(4000) );

Table created.

ops$tkyte%ORA10GR2> insert into t values ( 1, 'hello' );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2( x int, y clob );

Table created.

ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T', 'T2', 'x, to_clob(y) y' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> pause

ops$tkyte%ORA10GR2> variable nerrors number
ops$tkyte%ORA10GR2> begin
  2          dbms_redefinition.copy_table_dependents
  3          ( user, 'T', 'T2',
  4            copy_indexes => dbms_redefinition.cons_orig_params,
  5            num_errors => :nerrors );
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print nerrors

   NERRORS
----------
         0

ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop table t2;

Table dropped.

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T"
   (    "X" NUMBER(*,0),
        "Y" CLOB,
         CONSTRAINT "SYS_C0038059" PRIMARY KEY ("X")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE NOVALIDATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 LOB ("Y") STORE AS (
  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))



ops$tkyte%ORA10GR2> select * from t;

         X
----------
Y
-------------------------------------------------------------------------------
         1
hello





c) create table as select

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.


and you rated our response

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Brilliant

June 10, 2009 - 1:25 am UTC

Reviewer: chandini paterson

Tom,
Thank you for getting back so soon.
I was trying to avoid option 1 above, if I could. Though it is probably the most straight forward way of doing it.
The dbms_redefinition package looks interesting. I am going to have a play around with it to find out how it works.
If I do convert the varchar2 into a lob though, I would have to make some changes to the dependent stored procedures wouldn't I? I'm trying to create a tiny test here to see what that would entail. But any input from you would be great.

Thank you so much,
Chandini



Tom Kyte

Followup  

June 10, 2009 - 12:04 pm UTC

the stored procedures will work with up to 32k of data - beyond that, and they will need to change since the longest string in plsql is 32k.

Well, they will work assuming recent versions of Oracle anyway (9i and above). Clobs look a lot like big strings in current releases.

alternative solution

February 08, 2010 - 7:16 am UTC

Reviewer: Berry Bos from Meppel, The Netherlands.

Hi Tom,

We found an alternative solution which always preserves the order of columns.
The idea is to first convert the varchar2 column to a long and then to convert that long to a clob.

alter table t add y_copy varchar2(4000);
update t set y_copy = y;
update t set y = null;
commit;
alter table t modify y long;
alter table t modify y clob;
update t set y = y_copy;
alter table t drop column y_copy;

The last solution is good. Here are some suggestions

August 24, 2011 - 4:02 pm UTC

Reviewer: Venkatesh Malepati from CT, USA

You need to have a commit after "update t set y = y_copy;"

Otherwise you loose all the data, because the next statement drops the column and you will never able to recover.



alter table t add y_copy varchar2(4000);
update t set y_copy = y;
update t set y = null;
commit;
alter table t modify y long;
alter table t modify y clob;
update t set y = y_copy;
alter table t drop column y_copy;


Correct Version as follows:

alter table t add y_copy varchar2(4000);
update t set y_copy = y;
' Commit is here itself is not going to harm.
' Extra line
Commit;
update t set y = null;
commit;
alter table t modify y long;
alter table t modify y clob;
update t set y = y_copy;
' Definitely need a commit
Commit;
alter table t drop column y_copy;

Tom Kyte

Followup  

August 30, 2011 - 3:58 pm UTC

the alter table does the commit.

ops$tkyte%ORA10GR2> update t set temp=y, y=null;

1 row updated.

ops$tkyte%ORA10GR2> alter table t drop column y;

Table altered.


is "safe", the alter table does a COMMIT, then it drops the column, then it either commits (if successful on the drop) or rolls back (just the drop). The update is committed.




Definitely do not need a commit there, it was there all along.

Your extra commit I don't like

Add new column with CLOB

September 10, 2013 - 2:20 pm UTC

Reviewer: Sandeep from MH India

Hi Tom
I have one huge table with 400 M records. I have to add extra column in that table with datatype as clob, and copy data from same table into this newly added column.

Someone suggested me to use below method.

Alter table tt1 add column cc1 ( CLOB);

update table tt1 set CC1= CC2;

But I simply rejected this approach seeking constraint of amount of UNDO it will generate and time it will take.

Then next approach came to me was to spilt this and commit each after 1 M record by using PL/SQL block.

I dont want to do this as well.]

My database is 11G. Can you please guide me.
Tom Kyte

Followup  

September 23, 2013 - 5:32 pm UTC

use create table as select, create a new table - you are doing to update every row, you will lock the entire table, instead of creating gobs of redo and undo - just to a nice big create table as selelct


create table new
select ..... to_lob(cc1) as cc2 from t;


drop table t;
rename new to t;


Senior Engineer

March 31, 2015 - 1:53 pm UTC

Reviewer: Mingwei Wu from Virginia USA

The example is very useful and readable.

June 01, 2015 - 12:31 am UTC

Reviewer: Sandeep S from India

I tried to add a new column and it gave me the below error.

SQL> alter table PACE.TABLE_TE1 add CONDITION_COPY VARCHAR2(4000);
alter table PACE.TABLE_TE1 add CONDITION_COPY VARCHAR2(4000)
                                                 *
ERROR at line 1:
ORA-01792: ??????????????????1000???


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@g2412audlpdbs03 ~]$ oerr ora 01792
01792, 00000, "maximum number of columns in a table or view is 1000"
// *Cause: An attempt was made to create a table or view with more than 1000
//         columns, or to add more columns to a table or view which pushes
//         it over the maximum allowable limit of 1000. Note that unused
//         columns in the table are counted toward the 1000 column limit.
// *Action: If the error is a result of a CREATE command, then reduce the
//         number of columns in the command and resubmit. If the error is
//         a result of an ALTER TABLE command, then there are two options:
//         1) If the table contained unused columns, remove them by executing
//            ALTER TABLE DROP UNUSED COLUMNS before adding new columns;
//         2) Reduce the number of columns in the command and resubmit.


I tried to drop the unused columns and that did not work as well. I got the same error.

SQL> alter table PACE.TABLE_TE1 drop unused columns;

Table altered.

SQL> alter table PACE.TABLE_TE1 add CONDITION_COPY VARCHAR2(4000);
alter table TABLE_TE1 add CONDITION_COPY VARCHAR2(4000)
                                                 *
ERROR at line 1:
ORA-01792: ??????????????????1000???


August 06, 2015 - 1:41 pm UTC

Reviewer: A reader


More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here