Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: CLOB to VARCHAR2
In article <3bd40c77.252711620_at_news.colt.net>, withheld_at_address.com says...
>
>
>Hi,
>
>I need convert the type of a column from a CLOB to a VARCHAR2 without
>having to re-import millions of rows of data again.
>
> Existing table:
>
> CREATE TABLE my_table
> (
> id NUMBER(8),
> value CLOB
> );
>
>I need to convert the CLOB to a VARCHAR2, but...
>
> ALTER TABLE my_table MODIFY (value VARCHAR2(2000));
>
>yields an error. The manual says I should create a new column and copy
>the data.
>
> ALTER TABLE my_table ADD (new_value VARCHAR2(2000));
>
>Ok, so how do I copy the CLOB data into the VARCHAR2??
>
>Cheers,
>
>Andy
>
update my_table set new_value = dbms_lob.substr( value, 2000, 1 ), value = NULL;
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Mon Oct 22 2001 - 08:40:16 CDT