• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3922
  • Last Modified:

Processing large xml files using Oracle PL/SQL

Dear Experts,

I am trying to load an xml file using pl/sql and process each node within.  I have it working for a file that contains 19678 rows or less.  Unfortunately, when I attempt to process a file containing 85000 rows my program stops processing at 19678 rows.  Using another computer yields results close to (but not exactly) this number of rows.

The attached code snippet is a shortened and incomplete version of my code.

I suspect my problem is that I am using a DOM Document which loads the entire contents of the XML file.  Do I have the option of using a SAX parser in PL/SQL?  Is there some other way to solve this file size issue?

We are using Oracle 10.x.  Many thanks for any assistance, suggestions, or comments.

Best regards,

Bill
l_source_loc        bfile;
    l_source_offset     number := 1;
    l_target_loc        clob;
    l_target_offset     number := 1;
    l_lang_ctx          number := dbms_lob.default_lang_ctx;
    l_warning           number;

    l_dom_doc           dbms_xmldom.DOMDocument;
    l_root_node         dbms_xmldom.DOMNode;
    l_node              dbms_xmldom.DOMNode;
    l_xml               sys.xmltype := null;

begin

    l_source_loc := bfilename('PVM_LOAD_DIR', 'submission_file.xml') ;

    dbms_lob.fileopen(l_source_loc, dbms_lob.file_readonly);

    --** create target object
    dbms_lob.createtemporary (l_target_loc, TRUE);

    --** load target from source
    dbms_lob.loadclobfromfile(l_target_loc, l_source_loc,
                              dbms_lob.getlength(l_source_loc), l_target_offset, l_source_offset,
                              dbms_lob.default_csid, l_lang_ctx, l_warning);

    --** Create the XML DOM document
    l_dom_doc := dbms_xmldom.newDOMDocument( l_target_loc );

    l_root_node := dbms_xmldom.makeNode( l_dom_doc );
    
    i := 0;
                                         
    ... for each node
    l_node := dbms_xslprocessor.selectSingleNode( l_root_node, '/load_data/dm_rpt_submission_t[position()=' || to_char(i) || ']') );
    ... process the node
    
    i := i + 1;
    ... next node

end

Open in new window

0
bilpar
Asked:
bilpar
  • 3
1 Solution
 
oleggoldCommented:
here are SQL functions: XMLELEMENT, XMLFOREST, etc and PL/SQL functions: DBMS_XMLGEN, DBMS_XMLQUERY,
0
 
oleggoldCommented:
Try to use this:
CREATE OR REPLACE PROCEDURE loadxml
(PTABLE IN VARCHAR2 DEFAULT 'HAZMANA_AV_LDR' )
IS
ERR_MESS VARCHAR2(700);
fil    BFILE;
v_clob   CLOB;
fille UTL_FILE.FILE_TYPE;
LINESNUM INTEGER;
VARBUFF VARCHAR2(32767);
buffer RAW(32767);
BBUFF CLOB;
len     FLOAT;    
insrow INTEGER;
rowchunk INTEGER;
pos INTEGER;
pose INTEGER;
indx INTEGER;
posver INTEGER;
ins_num INTEGER:=650;
/*XMLROWS_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('<ROW>');
XMLROOTS_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('<ROWSET>');
XMLROWE_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('</ROW>');
XMLROOTE_TAG RAW(377):=UTL_RAW.CAST_TO_RAW('</ROWSET>');*/
XMLROWB VARCHAR2(1777);
DATAROWB VARCHAR2(27777);
XMLVER VARCHAR2(377);
XMLROWS_TAG VARCHAR2(377):='<ROW>';
XMLROOTS_TAG VARCHAR2(377):='<ROWSET>';
XMLROWE_TAG VARCHAR2(377):='</ROW>';
XMLROOTE_TAG VARCHAR2(377):='</ROWSET>';
XMLROWD RAW(32767);
insCtx DBMS_XMLSave.ctxType;
BEGIN
fil:=BFILENAME('XML_DIR',PTABLE||'.XML');
DBMS_LOB.FILEOPEN(fil,DBMS_LOB.FILE_READONLY);
len := DBMS_LOB.GETLENGTH(fil);
-- LINESNUM:=DBMS_LOB.GETCHUNKSIZE(fil);
dbms_lob.createtemporary(v_clob, cache=>FALSE);
if len<ins_num then
ins_num:=len;
end if;

  -- Private type declarations
--  type <TypeName> is <Datatype>;
   
-- xmlgen.setmaxrows(ins_num);
/*xmlgen.resetOptions;  
xmlgen.setIgnoreTagCase(xmlgen.IGNORE_CASE); */
-- xmlgen.setRowsetTag('ROWSET') ;

-- ins_num:=len/100000000;


-- DBMS_LOB.READ(fil,rowchunk,1,buffer);
-- UTL_FILE.GET_LINE(fille,VARBUFF);
 DBMS_LOB.LOADFROMFILE(v_clob,fil,len);
-- ins_num:=dbms_lob.getchunksize(v_clob);
-- buffer:=dbms_lob.converttoclob(
/*posver:=DBMS_LOB.INSTR(v_clob,XMLROOTS_TAG)-1;
XMLVER:=dbms_lob.substr(v_clob,posver);*/
 insCtx := DBMS_XMLSave.newContext(PTABLE); -- get the context handle
insrow := dbms_xmlsave.insertXML(insCtx,v_clob);
IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);
END IF;
indx:=1;
/*rowchunk:=len/ins_num;
for i in 1..ins_num   loop
pos:=DBMS_LOB.INSTR(v_clob,XMLROWS_TAG,1,i);
pose:=DBMS_LOB.INSTR(v_clob,XMLROWE_TAG,1,i)-50;
DATAROWB :=dbms_lob.substr(v_clob,pose,pos);
ins_num:=LENGTH(DATAROWB);
VARBUFF:=XMLVER||XMLROOTS_TAG||DATAROWB||XMLROOTE_TAG;
-- insrow := xmlgen.insertXML(''||PTABLE||'',VARBUFF);
-- v_clob:=dbms_lob.substr(v_clob,pose,);
-- len:=dbms_lob.getlength(v_clob);
len:=len-ins_num;
end loop;*/
  dbms_lob.freetemporary(v_clob);
-- len:=len-ins_num;
 
-- insrow := xmlgen.insertXML(''||PTABLE||'',UTL_RAW.CAST_TO_VARCHAR2(buffer));


-- DBMS_OUTPUT.PUT_LINE(insrow);

EXCEPTION   WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception');
DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE));
ERR_MESS:=SQLERRM;
/*IF DBMS_LOB.FILEISOPEN(fil) = 1 THEN
DBMS_LOB.FILECLOSE(fil);   END IF;*/
end loadxml;
0
 
slightwv (䄆 Netminder) Commented:
first:  If you call 'createtemporary' and 'newDOMDocument' you will also want to free them with the respective 'freetemporary' and 'freeDocument'.



I believe you are probably right in thinking it's a DOM/Memory issue.  There's also a strong chance it's a bug of some type.

I can see where this approach could be pretty slow and intensive with the way you have the loop set up.

I've read about a SAX processor with Oracle but to date, still cannot find any specific examples.

I would look at returning a DOMNodeList and use that in your loop.  Below is a code stub I used in a previous question.

Since you mention a 'file':  Depending on your specific requirements, you can trick SQL*Loader into processing an XML document.  Because of this, you can set up an External table to process the file.

For information on the External table method check out the solution to:
http://www.experts-exchange.com/Database/Oracle/Q_24963967.html



drop table tab1;
create table tab1(
	jobNumber varchar2(50),
	startTime	varchar2(50)
)
/

create or replace procedure junk(inXml in clob)
is
	doc			dbms_xmldom.DOMDocument;
	Node		dbms_xmldom.DOMNode;
	Node1		dbms_xmldom.DOMNode;
	NodeList	dbms_xmldom.DOMNodeList;

	num_nodes	number;
	i			number;

	jobNumber varchar2(50);
	startTime varchar2(50);
begin
	doc := dbms_xmldom.newDomDocument(inXml);
	NodeList := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(doc),'/Nuisance/Jobs/*');
	num_nodes := dbms_xmldom.getLength(NodeList);

	for i in 0..num_nodes-1 loop
		node1 := dbms_xmldom.item(nodeList, i);
		jobNumber := dbms_xslprocessor.valueOf(node1,'./@JobNumber');
		startTime := dbms_xslprocessor.valueOf(node1,'./@StartTime');

		insert into tab1 values( jobNumber, startTime );

	end loop;
end;
/

show errors


exec junk(' -
<Nuisance> -
<Jobs> -
<Job Status="STOPPED" JobNumber="695" JobName="w0_xm4" StartTime="09:12:15" /> -
<Job Status="STOPPED" JobNumber="696" JobName="w0_xm2" StartTime="15:17:33"/> -
</Jobs> -
</Nuisance> -
' -
);

commit;

select * from tab1;

Open in new window

0
 
bilparAuthor Commented:
Sorry for the delay.  Many thanks for this solution.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now