Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Conerting VARCHAR2 to CLOB
Thanks, that works great.
I got around the original problem by adding a CLOB field to an existing table and writing and selecting from that field. Using a global temporary table is a much better option .... Unfortunately I know we have an issue with 8.1.6 on Solaris generating internal errors with global temp tables.
Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:<9te51g025jq_at_drn.newsguy.com>...
> In article <feb77408.0111200228.36816810_at_posting.google.com>,
> robburton_at_totalise.co.uk says...
> >
> >Unfortunately, I still get the same result. The only difference is
> >Oracle is 8.1.6.2 running on Solaris.
>
>
> ok, try this:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create global temporary table t ( x clob ) on
> commit delete rows;
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace package types
> 2 as
> 3 type rc is ref cursor;
> 4 end;
> 5 /
>
> Package created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> 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 l_rid rowid;
> 7 begin
> 8 insert into t values ( empty_clob() )
> 9 returning x, rowid into l_lob, l_rid;
> 10 dbms_lob.writeappend
> 11 (l_lob,p_length,rpad(p_char,p_length,p_char));
> 12
> 13 open p_cursor for select l_lob from t where rowid=l_rid;
> 14 end;
> 15 /
>
> Procedure created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> variable x refcursor
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set autoprint on
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> REM just a little bit
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> set long 50
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> exec p(:x,'A',12345);
>
> PL/SQL procedure successfully completed.
>
>
> :B1
> --------------------------------------------------
> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>
> 1 row selected.
>
>
> That should work -- i reproduced your issue on 817 solaris with the other
> method.
Received on Tue Nov 27 2001 - 09:45:38 CST
![]() |
![]() |