Re: Conerting VARCHAR2 to CLOB

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9t3bau02oe4_at_drn.newsguy.com>...
> 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 )
> is
> 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 );

This was how I originally tried to get the result out, but for both my original test and your version Oracle throws a -

ORA-22922: nonexistent LOB value error.

Cause: The LOB value associated with the input locator does not exist. The information in the locator does not refer to an existing LOB.

Action: Repopulate the locator by issuing a SELECT statement and retry the operation.

Is it something to do with writing and then reading to the lob in one transaction, or a problem with 8.1.6???

Thanks

Rob.. Received on Mon Nov 19 2001 - 05:04:55 CST