Returning > 4k from a pl/sql function

From: Jeremy Ovenden <jovenden_at_hazelweb.co.uk>
Date: Fri, 16 Nov 2001 16:01:57 -0000
Message-ID: <tvae3o73n8tg0c@corp.supernews.com>

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

Thanks.....

--
Jeremy

Received on Fri Nov 16 2001 - 10:01:57 CST