Re: CLOB to VARCHAR2

On 22 Oct 2001 06:40:16 -0700, Thomas Kyte <tkyte_at_us.oracle.com> wrote:

>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;

Cheers, Tom! You've just saved my arse!

Andy

>
>--
>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 - 11:22:35 CDT