Generating XML PLSQL Iterative

Question

I am trying to build some very large XML files via PL/SQL in Oracle 11g. I am trying to build the files iteratively - fetch one row, write to file, get next row, etc. Below is my code. I am having issues on where to define the CLOB.

I get two errors depending on where I initialize and free the CLOB: 1-Exceeding memory -- when initializing CLOB before loop and freetemporary after loop 2- Can't find the clob(invalid LOB locator specified) -- when initialize in loop and free in loop, or initialize out and free in loop

Please advise what the issue is in my approach or what the best approach is to build a large XML file iteratively.

PROCEDURE sql_to_xml(p_sql IN VARCHAR2,
                    p_fileName       IN VARCHAR2,
                    p_dir            IN VARCHAR2,
                    p_xml_created OUT VARCHAR2) IS

xml_result CLOB;
doc        dbms_xmldom.DOMDocument;
ctx DBMS_XMLGEN.ctxHandle;
vv_exit_code varchar2(5);
vv_ctx_open varchar2(1) := 'N';
max_rows NUMBER := 5;

BEGIN

vv_exit_code := 'XML1';
ctx := dbms_xmlgen.newcontext(p_sql);
vv_ctx_open := 'Y';
DBMS_OUTPUT.put_line(vv_exit_code);

vv_exit_code := 'XML2';
DBMS_XMLGEN.SETCONVERTSPECIALCHARS (ctx,TRUE);
DBMS_OUTPUT.put_line(vv_exit_code);

DBMS_LOB.CREATETEMPORARY(xml_result,true); 
while DBMS_XMLGEN.GETNUMROWSPROCESSED(ctx) < max_rows
LOOP
    vv_exit_code := 'XML3';
    xml_result := dbms_xmlgen.getXML(ctx);
    DBMS_OUTPUT.put_line(vv_exit_code);
    DBMS_output.put_line('Xml result is: ' ||dbms_lob.substr( xml_result, 4000, 1 ));

    IF xml_result is not null THEN
        vv_exit_code := 'XML4';    
        doc := dbms_xmldom.newDOMDocument(xml_result);
        DBMS_OUTPUT.put_line(vv_exit_code);

        vv_exit_code := 'XML5';
        dbms_xmldom.writeToFile(doc,p_dir||'/'||p_fileName, 'ISO-8859-1');
        DBMS_OUTPUT.put_line(vv_exit_code);

        vv_exit_code := 'XML6';
        dbms_xmldom.freeDocument(doc);
        p_xml_created := 'TRUE';
        DBMS_OUTPUT.put_line(vv_exit_code);

    ELSE
        p_xml_created := 'FALSE';
    END IF;

    DBMS_OUTPUT.PUT_LINE('XML Result: '||xml_result);
        dbms_lob.FREETEMPORARY(xml_result);

end loop;


DBMS_XMLGEN.CLOSECONTEXT (ctx);
vv_ctx_open := 'N';

EXCEPTION
WHEN out_of_process_memory THEN
    IF vv_ctx_open = 'Y' THEN
        DBMS_XMLGEN.CLOSECONTEXT (ctx);
    END IF;

    gv_err_msg := substr(sqlerrm,1,2000);
    DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
    RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
    dbms_output.put_line('XML_EXPORT failed (out_of_process_memory exception) executing '||p_sql);
    raise_application_error(-20906,'XML_EXPORT (out_of_process_memory exception) failed executing '||p_sql);


WHEN OTHERS THEN
    IF vv_ctx_open = 'Y' THEN
        DBMS_XMLGEN.CLOSECONTEXT (ctx);
    END IF;
    if xml_result is NULL then
        gv_err_msg := substr(sqlerrm,1,2000);
        DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
        --   RAISE_APPLICATION_ERROR(-20906,gv_process_name||' failed'||gv_err_msg);
        dbms_output.put_line('XML_EXPORT failed (xml results are NULL) executing '||p_sql);
        raise_application_error(-20906,'XML_EXPORT (xml results are NULL) failed executing '||p_sql);
    else
        gv_err_msg := substr(sqlerrm,1,2000);
        DBMS_OUTPUT.put_line(gv_process_name||' failed '||gv_err_msg);
          dbms_output.put_line('XML_EXPORT failed (others exception) executing '||p_sql);
        DBMS_OUTPUT.put_line('Export Directory is: '||p_dir||'/'||p_fileName);
        raise_application_error(-20906,'XML_EXPORT (others exception) failed executing '||p_sql);
    end if;
END sql_to_xml;

Answer 1

There's no point attempting to generate very large XML in PL/SQL. The problem isn't PL/SQL as such, but that PL/SQL only supports XML DOM, and DOM does not handle large XML at all well. You don't say what size of XML document you have, but I would not be surprised to find the memory being used by PL/SQL to build your document being about 10 to 30 times the size of the resulting document.

Is there an option to generate the XML using something other than PL/SQL? If not, and I really had to generate large XML files in an Oracle database, I would consider using Java stored procedures. This question has some answers on how to do this kind of thing in Java.

EDIT in response to your comment: your code is most definitely not writing one line at a time. It is writing the lot together, a fact I verified by running it using the query SELECT * FROM all_objects on my Oracle 11g XE database. The loop ran once and wrote 7341 objects, creating an XML file just over 3MB in size.

I then tried modifying your code to better support the 'incremental' approach you describe. This involved:

• adding a line dbms_xmlgen.setmaxrows(ctx, max_rows); to tell DBMS_XMLGEN to only generate 5 rows at a time. Otherwise it attempts to generate the lot in one go.

• modifying the code at the top of the WHILE loop to

  xml_result := dbms_xmlgen.getXML(ctx);
  num_rows_processed := DBMS_XMLGEN.GETNUMROWSPROCESSED(ctx);
  dbms_output.put_line('Got ' || num_rows_processed || ' rows processed');
  
  while num_rows_processed > 0
    -- rest of loop omitted
  

• adding the first of these three lines just before the bottom of the WHILE loop.

I then re-ran your code, and I could see it writing each batch of five rows to the file each time. However, there is a slight issue with this approach, in that the file was overwritten each time. At the end I had only a single record in the output XML file. I can't imagine this would be what you want.

The WRITETOCLOB, WRITETOBUFFER and WRITETOFILE methods in DBMS_XMLDOM don't hint at the ability to append to an existing file, and to be honest I'm not surprised that they don't. If you could, you would end up with invalid XML, as there would be more than one <?xml ... ?> declaration in the file.

I stand by my previous advice. Whenever you need to deal with large XML, in an Oracle database or elsewhere, use SAX or StAX. PL/SQL doesn't support either, so do whatever you need to do in Java stored procedures or do it out of the database.