| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Conerting VARCHAR2 to CLOB
In article <feb77408.0111190304.4ebcfa72_at_posting.google.com>,
robburton_at_totalise.co.uk says...
>
>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:
>>
[snip]
>
>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..
Are you sure? I did the example in 816:
tkyte_at_TKYTE816> create or replace package types
2 as
3 type rc is ref cursor;
4 end;
5 /
Package created.
tkyte_at_TKYTE816> create or replace procedure p ( p_cursor in out types.rc,
2 p_char in varchar2,
3 p_length in number )
4 is
5 l_lob clob;
6 begin
7 dbms_lob.createtemporary( l_lob, TRUE );
8 dbms_lob.writeAppend( l_lob, p_length, rpad(p_char,p_length,p_char) );
9 open p_cursor for select l_lob from dual;
10 end;
11 /
Procedure created.
tkyte_at_TKYTE816> variable x refcursor tkyte_at_TKYTE816> set autoprint on tkyte_at_TKYTE816> REM just a little bit tkyte_at_TKYTE816> set long 50 tkyte_at_TKYTE816> exec p( :x, 'A', 12345 );
PL/SQL procedure successfully completed.
:B1
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
tkyte_at_TKYTE816> exec p( :x, 'z', 32765 );
PL/SQL procedure successfully completed.
:B1
zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz
tkyte_at_TKYTE816>
tkyte_at_TKYTE816> select * from v$version
2 /
BANNER
-- 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 CorpReceived on Mon Nov 19 2001 - 08:11:28 CST
 |
 |