Re: CLOB to VARCHAR2

In article <cfb1f4b0.0110291326.6b75f987_at_posting.google.com>, meghan_os_at_yahoo.com says...
>
>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'
>
>

lets see a describe of NOTES

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> create table notes ( notes clob, notes_var varchar2(4000));

Table created.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> insert into notes ( notes ) values ( rpad('*',4000,'*') );

1 row created.

ops$tkyte_at_ORA717DEV.US.ORACLE.COM> update notes set notes_var = dbms_lob.substr(notes,4000);

1 row updated.

if its a clob, it should work OK. Also -- what is the version????

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

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