Re: CLOB to VARCHAR2

From: Meghan O'Sullivan <meghan_os_at_yahoo.com>
Date: 29 Oct 2001 13:26:33 -0800
Message-ID: <cfb1f4b0.0110291326.6b75f987@posting.google.com>

I am trying something similar to this email: UPDATE notes SET notes_var = dbms_lob.SUBSTR(notes, 4000); and I get the following error, any thoughts?

SQL> UPDATE notes SET notes_var = dbms_lob.SUBSTR(notes, 4000); UPDATE notes SET notes_var = dbms_lob.SUBSTR(notes, 4000)

                             *

ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'SUBSTR'

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9r17k002tfr_at_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;
Received on Mon Oct 29 2001 - 15:26:33 CST