| 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
Unfortunately, I still get the same result. The only difference is
Oracle is 8.1.6.2 running on Solaris.
The output is:
SQL> create or replace package types
2 as
3 type rc is ref cursor;
4 end;
5 /
Package created.
SQL> 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.
SQL> variable x refcursor SQL> set autoprint on SQL> REM just a little bit SQL> set long 50 SQL> exec p(:x,'A',12345);
PL/SQL procedure successfully completed.
ERROR:
ORA-22922: nonexistent LOB value
no rows selected
SQL> exec p(:x,'z',32765);
PL/SQL procedure successfully completed.
ERROR:
ORA-22922: nonexistent LOB value
no rows selected
SQL> select * from v$version;
BANNER
SQL> spool off
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9tb3ug01eu6_at_drn.newsguy.com>...
> 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
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
> PL/SQL Release 8.1.6.0.0 - Production
> CORE 8.1.6.0.0 Production
> TNS for 32-bit Windows: Version 8.1.6.0.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
Received on Tue Nov 20 2001 - 04:28:02 CST
 |
 |