Re: Conerting VARCHAR2 to CLOB

In article <feb77408.0111160250.66fbb9cd_at_posting.google.com>, robburton_at_totalise.co.uk says...
>
>I have a problem, using Oracle 8.1.6 .
>
>I have a varchar2(32767) variable in a PL/SQL procedure that passes
>the result back to the calling application in a REF CURSOR. Previously
>the string was a varchar2(4000) and could happily be passed back. In
>order to pass the resulting string back the Cursor variable has been
>changed to a CLOB.
>
>The problem is I can't convert the varchar2 string to a clob to pass
>back. How can I do this.
>
>The existing call fails because of the attempted conversion..
>
>open rc_clob_out for
> select varchar2_variable as clob_value,
> other_var1 as var1
> from dual;
>
>Any help appreciated
>
>Thanks
>
>Rob..

Try this:

create or replace package types
as

    type rc is ref cursor;
end;
/

create or replace procedure p ( p_cursor in out types.rc,
                                p_char in varchar2,
                                p_length in number )

    l_lob clob;
begin

    dbms_lob.createtemporary( l_lob, TRUE );

    dbms_lob.writeAppend( l_lob, p_length, rpad(p_char,p_length,p_char) );

    open p_cursor for select l_lob from dual; end;
/

variable x refcursor
set autoprint on
set long 33000
exec p( :x, 'A', 12345 );
exec p( :x, 'z', 32765 );

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