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!

Answer 1

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! :)