Is there any way to return a clob as text (or file) without splitting into smaller pieces first?

I tried creating a GET handler with PL/SQL source that would just do this:

declare
  txt clob;
begin
  ...-- set clob
  htp.p(txt);
end;

But then I was getting the error ORA-06502: PL/SQL: numeric or value error.

I then tried cutting the clob in smaller segments and calling htp.p multiple times, which worked, but I was wondering if there was a way to send the whole thing in one go.

CodePudding user response：

The doc states that htp.p and htp.prn take only VARCHAR2 so you're limited by the max size of a varchar2 and if the clob length exceeds that it will throw an error. This is what you can do:

Loop through the clob in 4k chunks and output using htp.prn. Avoid using htp.p in a loop because that generates a newline characters which could mess up the output, for example if you're generating json. It's also good practice to let the browser know what he's getting by setting the mime header.

DECLARE
  l_clob  CLOB;
  l_amt   INTEGER := 4000;
  l_pos   INTEGER := 1;
  l_buf   VARCHAR2(4000);
BEGIN
    owa_util.mime_header ('text/html', true);
    l_clob := '....';
    LOOP
        BEGIN
        dbms_lob.read(
                    l_clob,
                    l_amt,
                    l_pos,
                    l_buf
        );
        l_pos := l_pos   l_amt;
        -- need htp.prn since htp.p generates a newline char at the end.
        htp.prn(l_buf);
        EXCEPTION
        WHEN no_data_found THEN
            EXIT;
        END;
    END LOOP;
END;