How to query a specific number of rows in order to avoid buffer overflow when spooling XML result

Question

I have a script that runs a sqlplus command in order to generate a XML result and spool it to a file.

Everything goes well except when I do it on a big table. A buffer overflow exception is raised when I get a clob greater than 1000000 bytes...

I am then looking for a way to perform multiple SELECT statements depending on that clob size. For example: If I get a clob of 4 000 000 bytes, I would perform 4 select, each of these on 1/4 of the rows of my table. Do you have any idea of how I can do this?

Here is an example of my code:

sqlplus -s "DB" << EOF > /dev/null

set serveroutput on size unlimited;
set feedback off;
set pagesize 0;
set linesize 300;
set trimspool on;
set trimout on;
set termout off;
set wrap on;
set arraysize 200;

spool file.xml append;

DECLARE
   l_xmltype XMLTYPE;
   l_ctx dbms_xmlgen.ctxhandle;
   l_var VARCHAR(40);
   v_clob CLOB;
   v_clob_length INTEGER;
   amount INTEGER = 32767;
   buffer VARCHAR2(32767);
   pos INTEGER;

BEGIN

     l_ctx := dbms_xmlgen.newcontext('SELECT a.* FROM '||tableName||' a');

     dbms_xmlgen.setrowsettag(l_ctx, 'Table-'||tableName); 
     dbms_xmlgen.SetNullHandling(l_ctx, dbms_xmlgen.EMPTY_TAG);

     l_xmltype := dbms_xmlgen.getXmlType(l_ctx);
     dbms_xmlgen.closeContext(l_ctx);

     SELECT XMLSERIALIZE(DOCUMENT l_xmltype AS CLOB) into v_clob FROM dual;
     v_clob_length := length(v_clob);

     IF v_clob_length > 1000000 THEN 

        -- HERE (buffer overflow raised)
        return;

     END IF;

     WHILE pos < v_clob_length LOOP
        dbms_lob.read(v_clob, amount, pos, buffer);
        dbms_output.put_line(buffer);
        pos := pos + amount;
     END LOOP;

END;
/
spool off;

EOF

If you have a better idea to solve this, let me know! :)

Thanks a lot!

What exception is raised? We don't know what limitation you're hitting if we don't know what it complains about. The code you've posted doesn't produce any output anyway, but have you perhaps removed `dbms_output` calls for posting (why!?) and are hitting a buffer limit? Otherwise what are you actually spooling? Also, are you running this on the server or a separate client machine - you might be able to use `utl_file` instead of spool if the output file ends up on the server. — Alex Poole, May 21 '19 at 16:04
I removed the dbms_output because it wasn't the concerned part of the code, I just output the clob resulted. The exception raised is a buffer overflow because the output that I want to spool is too large (>1 000 000 bytes) and I can't use the utl_file because i'm, as you said, on a separate client machine :) Everything works well until i query a table generating a too big xml result, that's my problem... — Wallarou, May 21 '19 at 17:11
" I just output the clob " - where? There is no output of any kind in the code. — OldProgrammer, May 21 '19 at 19:58
Have you got the sqlcl client installed? (It's the SQL\*Plus replacement) It makes exporting in XML format dead easy. [Find out more](https://oracle-base.com/articles/misc/sqlcl-format-query-results-with-the-set-sqlformat-command#xml). To be honest I don't know how nicely it plays with CLOBs but it might be worth a try. — APC, May 23 '19 at 15:26

score 0 · Accepted Answer · answered May 24 '19 at 07:23

It's okay, I found a solution!

For those who would have this problem:

I just added a condition to verify that the clob resulted is greater than 1 000 000 bytes. If so, I divide that clob size by 1000000 and get an integer, I retreive the number of rows of my table and then I loop by selecting only a part of the rows at each iteration.

Here is the code:

IF v_clob_length > 1000000 THEN
   nbr_iteration := (v_clob_length/1000000)+1;
   execute immediate 'SELECT count(*) FROM '||myTable into nbr_rows;
   FOR i in 1..nbr_iteration LOOP
     current_calc := ((i/nbr_iteration)*nbr_rows)+1;
     l_ctx := dbms_xmlgen.newcontext('SELECT t.rowid, t.* FROM (SELECT t.*, row_number() over (order by rowid) rowNumber FROM '||myTable||' t) t WHERE rowNumber BETWEEN '||previous_calc||' AND '||current_calc||'');
     previous_calc := current_calc+1;
   END LOOP;
END IF;

I know my question wasn't really clear but thanks anyway! :)

How to query a specific number of rows in order to avoid buffer overflow when spooling XML result

1 Answers1