Re: Returning > 4k from a pl/sql function

In article <tvae3o73n8tg0c_at_corp.supernews.com>, "Jeremy says...
>
>Hi, is it possible to have a function that returns > 4096 bytes. If not, are
>there any elegant workarounds?
>
>My particular example is that I wish to return the text value from a CLOB to
>a calling program, so I have a function thus (nabbed from the Ask Tom
>site!).
>
>
>create or replace function get_text_from_clob( p_clob in clob )
>return
>is
> l_lob clob;
> l_amt number default 15000;
> l_off number default 1;
> l_data varchar2(32000);
> i number := 0;
>begin
>
> begin
> loop
> i := i +1;
> dbms_lob.read( p_clob, l_amt, l_off, l_data );
>-- htp.prn( l_data );
> l_off := l_off+l_amt;
> l_amt := 15000;
> end loop;
> exception
> when no_data_found then
> NULL;
> when others then
> dbms_output.put_line('Error - iteration '||i);
> end;
> return l_data;
>
>end;
>/
>
>
>It runs fine, however if it tries to return l_data when l_data is > 4096, I
>get an ORA-06502 numeric or value error
>

Jeremy, long time no hear...

Anyway, the limit is actually exactly 4,000 bytes -- you would not even need that little function to do it -- you would just code:

select dbms_lob.substr( col_col, 4000, 1 ) from t;

The maximum string that can be returned via SQL is 4,000 bytes. You would have to return the actual clob and then let the client use the clob to retrieve the data.

>Thanks.....
>
>--
>Jeremy
>
>

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