Error while processing Large XML File

[pawansinghal]

Hi,

I am getting the following error when trying to read/process XML file of around 4-5 MB size:

ERROR at line 1:
ORA-04031: unable to allocate 2520 bytes of shared memory ("large
pool","unknown object","session heap","koh-kghu session heap")
ORA-06512: at "VMI_USER.PKG_VMI_FILE_UPLOAD", line 978
ORA-04031: unable to allocate 1000 bytes of shared memory ("large
pool","unknown object","qmxlu subheap","qmemNextBuf:alloc")
ORA-06512: at line 1

I am using Oracle 10g and having the following method to process the xml file to upload it's contents in the database table conditionally:

PROCEDURE pr_process_xml_file(pin_xml_file_name IN VARCHAR2)
IS
l_bfile BFILE;
l_clob CLOB;
l_parser dbms_xmlparser.parser;
l_doc dbms_xmldom.domdocument;
l_nl dbms_xmldom.domnodelist;
l_n dbms_xmldom.domnode;
l_nl_party dbms_xmldom.domnodelist;
l_n_party dbms_xmldom.domnode;
l_nl_nad dbms_xmldom.domnodelist;
l_n_nad dbms_xmldom.domnode;
l_nl_line dbms_xmldom.domnodelist;
l_n_line dbms_xmldom.domnode;

begin
-- the xml file to process
l_bfile := BFileName('DATA_DIR',pin_xml_file_name);

-- Create temporary lob
dbms_lob.createtemporary(l_clob, cache=>FALSE);

-- open xml file in readonly mode
dbms_lob.OPEN(l_bfile, dbms_lob.lob_readonly);

-- load the file contents in the clob
dbms_lob.loadfromfile(l_clob,l_bfile,dbms_lob.getlength(l_bfile));

-- close the xml file
dbms_lob.CLOSE(l_bfile);


-- create a parser
l_parser := dbms_xmlparser.newparser;

-- parse the document and create a new DOM document
dbms_xmlparser.parseclob(l_parser,l_clob);

l_doc := dbms_xmlparser.getdocument(l_parser);

-- free resources associated with the parser now it is no longer required
dbms_lob.freetemporary(l_clob);
dbms_xmlparser.freeparser(l_parser);

-- get a list of all the DOCUMENT nodes in the document using the XPATH systax
l_nl := xslprocessor.selectnodes(xmldom.makenode(l_doc),'/TRADING/DOCUMENT');


-- loop thru the list and create a DOCUMENT record
FOR cur_doc IN 0 .. xmldom.getlength(l_nl) - 1
LOOP
.. process the contents of file and load then in table.

END LOOP;

xmldom.freedocument(l_doc);
end;

Please help.
Thanks - Pawan

[tabreaz]

i too had the same problem, seems some limitation in processing XML files.

trying splitting the XML file

[pawansinghal]

Let me know if you have success splitting the file and how?

Thanks