Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: CLOB to VARCHAR2

Re: CLOB to VARCHAR2

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 22 Oct 2001 06:40:16 -0700
Message-ID: <9r17k002tfr@drn.newsguy.com>


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 Corp 
Received on Mon Oct 22 2001 - 08:40:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US