Re: Conerting VARCHAR2 to CLOB

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 20 Nov 2001 09:48:32 -0800
Message-ID: <9te51g025jq@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.

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

Received on Tue Nov 20 2001 - 11:48:32 CST