Database Software

My company has made the decision to do all of our internal inter-system communication using XML. Often we may need to transfer thousands of records from one system to another and due to this (and the 32K limit in prior versions) we're implementing it in 11g. Currently we have Oracle 11g Enterprise Edition Release 11.1.0.6.0 on 64 bit Linux.

This is a completely network/memory setup - the XML data comes in using UTL_HTTP and is stored in a CLOB in memory and then converted to a DOMDocument variable and finally the relevant data is extracted using xslprocessor.valueof calls.

While this is working fine for smaller datasets, I've discovered that repeated calls with very large documents cause the xslprocessor to run out of memory with the following message:

ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 21256 bytes
(qmxdContextEnc,)
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 1010
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 1036
ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 1044
ORA-06512: at "SCOTT.UTL_INTERFACE_PKG", line 206
ORA-06512: at line 28


Elapsed: 00:03:32.45
SQL>

From further testing, it appears that the failure occurs after approximately 161,500 calls to xslprocessor.valueof however I'm sure this is dependent on the amount of server memory available (6 GB in my case).

I expect that we will try and log a TAR on this, but my DBA is on vacation right now. Has anyone else tried calling the xslprocessor 200,000 times in a single session?

I've tried to make my test code as simple as possible in order to track down the problem. This first block simply iterates through all of our offices asking for all of the employees at that office (there are 140 offices in the table).

DECLARE
CURSOR c_offices IS
SELECT office_id
FROM offices
ORDER BY office_id;

r_offices C_OFFICES%ROWTYPE;

BEGIN

OPEN c_offices;
LOOP
FETCH c_offices INTO r_offices;
EXIT WHEN c_offices%NOTFOUND;

utl_interface_pkg.get_employees(r_offices.office_id);

END LOOP;
CLOSE c_offices;

END;

Normally I'd be returning a collection of result data from this procedure, however I'm trying to make things as simple as possible and make sure I'm not causing the memory leak myself.

Below is what makes the SOAP calls (using the widely circulated UTL_SOAP_API) to get our data and then extracts the relevant parts. Each office (call) should return between 200 and 1200 employee records.

PROCEDURE get_employees (p_office_id IN VARCHAR2)

l_request utl_soap_api.t_request;
l_response utl_soap_api.t_response;
l_data_clob CLOB;
l_xml_namespace VARCHAR2(100) := 'xmlns="' || G_XMLNS_PREFIX || 'EMP.wsGetEmployees"';
l_xml_doc xmldom.DOMDocument;
l_node_list xmldom.DOMNodeList;
l_node xmldom.DOMNode;
parser xmlparser.Parser;

l_emp_id NUMBER;
l_emp_first_name VARCHAR2(100);
l_emp_last_name VARCHAR2(100);

BEGIN

--Set our authentication information.
utl_soap_api.set_proxy_authentication(p_username => G_AUTH_USER, p_password => G_AUTH_PASS);

l_request := utl_soap_api.new_request(p_method => 'wsGetEmployees',
p_namespace => l_xml_namespace);

utl_soap_api.add_parameter(p_request => l_request,
p_name => 'officeId',
p_type => 'xsd:string',
p_value => p_office_id);

l_response := utl_soap_api.invoke(p_request => l_request,
p_url => G_SOAP_URL,
p_action => 'wsGetEmployees');

dbms_lob.createtemporary(l_data_clob, cache=>FALSE);
l_data_clob := utl_soap_api.get_return_clob_value(p_response => l_response,
p_name => '*',
p_namespace => l_xml_namespace);

l_data_clob := DBMS_XMLGEN.CONVERT(l_data_clob, 1); --Storing in CLOB converted symbols (<">) into escaped values (&lt;, &qt;, &gt;). We need to CONVERT them back.

parser := xmlparser.newParser;
xmlparser.parseClob(parser, l_data_clob);
dbms_lob.freetemporary(l_data_clob);

l_xml_doc := xmlparser.getDocument(parser);
xmlparser.freeparser(parser);

l_node_list := xslprocessor.selectNodes(xmldom.makeNode(l_xml_doc),'/employees/employee');

FOR i_emp IN 0 .. (xmldom.getLength(l_node_list) - 1)
LOOP

l_node := xmldom.item(l_node_list, i_emp);

l_emp_id := dbms_xslprocessor.valueOf(l_node, 'EMPLOYEEID');
l_emp_first_name := dbms_xslprocessor.valueOf(l_node, 'FIRSTNAME');
l_emp_last_name := dbms_xslprocessor.valueOf(l_node, 'LASTNAME');

END LOOP;

xmldom.freeDocument(l_xml_doc);

END get_employees;

All of this works just fine for smaller result sets, or fewer iterations (only the first two or three offices). Even up to the point of failure the data is being extracted correctly - it just eventually runs out of memory. Is there any way to free up the xslprocessor? I've even tried issuing DBMS_SESSION.FREE_UNUSED_USER_MEMORY but it makes no difference.