Re: Anybody resolved memory issues caused by XML for PL/SQL in 8.1.7 ?

Carlton,

My experience is with the XML parser in PL/SQL via XMLTYPE and the DBMS_XMLDOM package. In cases were I needed to process large XML files, I had lots of problems where the code would go "out to lunch" which I believe is symptomatic of memory leaks in their XDB engine for Oracle 9.2.0.4. I don't know if these packages are actually be implemented in Java behind the PL/SQL??? Anyway, our team concluded that with Oracle 9.2.0.4 you are best to avoid processing large XML files using iterations via a DBMS_XMLDOM methods ( see sample code below ). We had some other strange issues where ( in only a few cases ) if we had a list of repetive nodes in an XML Document, the code would go out to lunch on specific nodes for no apparent reason. Our workaround, as strange as it sounds, was to add a dummy attribute to these element nodes ( A="1" ). This allowed the node list iteration code to work. Go figure. In the end, processing large XML files outstide of Oracle's PL/SQL engine might be the best bet if it is an option for you. Surely the Java SDKs are not as buggy as Oracle's at this time. ( just look at the bug list on the http://www.metalink.oracle.com site for evidence of many of these bugs with XML )

Hope this helps,
Kurt

nListLength NUMBER := 0;
domDOC DBMS_XMLDOM.DOMDocument;
domElement DBMS_XMLDOM.DOMElement;
nodeRoot DBMS_XMLDOM.DOMNode;
nodeItem DBMS_XMLDOM.DOMNode;
nodesList DBMS_XMLDOM.DOMNodelist;

xmlRequest := XMLTYPE.CREATEXML(requestXML);

• Create DOM document handle domDOC := DBMS_XMLDOM.newDOMDocument(xmlRequest);
• Create document element handle ( root ) domElement := DBMS_XMLDOM.getDocumentElement(domDOC);
• Make document elemente a node nodeRoot := DBMS_XMLDOM.makeNode(domElement);
• see if node exists before entering loop, otherwise code fails

IF xmlRequest.existsNode('//Root/Data/Details/Action') = 1

THEN recAssetRecord.EventsExist := TRUE;

• Use XPATH pattern to retrieve the list of Item nodes nodesList := DBMS_XSLPROCESSOR.selectNodes(nodeRoot, '//Root/Data/Details');
• Expand table for each record to be added

nListLength := DBMS_XMLDOM.getLength(nodesList);

FOR i IN 0 .. nListLength - 1

LOOP

• Access node by index nodeItem := DBMS_XMLDOM.ITEM(nodesList, i);
• see SelectSingleNodeValClsc helper function below vcAction := BMQR_COMMON.SelectSingleNodeValClsc(nodeItem, 'Action'); etc...

etc...

END LOOP; FUNCTION SelectSingleNodeValClsc(node IN DBMS_XMLDOM.DOMNode, vcNodeName IN VARCHAR2) RETURN VARCHAR2 AS

nodeChild DBMS_XMLDOM.DOMNode;
nodeChildValue DBMS_XMLDOM.DOMNode;
vcNodeValue VARCHAR2(32767);

BEGIN vcNodeValue := ' ';
-- Get child of node parameter
nodeChild := DBMS_XSLPROCESSOR.selectSingleNode(node, vcNodeName);

IF (DBMS_XMLDOM.IsNull(nodeChild) = FALSE)

THEN

• if this node has children, then get the first one
• assuming it is the only child IF DBMS_XMLDOM.hasChildNodes(nodeChild)

THEN nodeChildValue := DBMS_XMLDOM.getFirstChild(nodeChild);

• Extract the value of this child node IF (DBMS_XMLDOM.IsNull(nodeChildValue) = FALSE)

THEN vcNodeValue := DBMS_XMLDOM.getNodeValue(nodeChildValue);

END IF; END IF; END IF; RETURN(COALESCE(vcNodeValue,
' '));

END SelectSingleNodeValClsc;

-- 


----------------------------------------------------
This mailbox protected from junk email by MailFrontier Desktop
from MailFrontier, Inc. http://info.mailfrontier.com

"Carlton Gregory" <carlton_gregory_at_yahoo.com> wrote in message
news:12e37858.0411220715.1cff072e_at_posting.google.com...


> Im looking for some insight into two bugs that I have seen.

>

> The error java.lang.ArrayIndexOutOfBoundsException seems to be a but when

you


> try to parse more then 2048 XML files.

> --Supposedly the fix for this was to install the

> 9I XDK into the 8.1.7.4 database.

>

> but once you do the above you get the below error.

>

> The java.lang.OutOfMemoryError error that occors when you parse a large

number


> of XML files is an issue in an 8.1.7.4 XDK.

> -- Now supposedly the fix for this was to upgrade to

> the 9.2.0.2 database because its XDB does not use

> java but uses PL/SQL.

>

> also Ive seen to increase the java_session_memory parameter but that does

not


> make a difference because it just reaches that setting and trys to go pass

that


> also.

>

> Has anybody had any success with this?

> It seems as though the actually number of XML files are around 999 befoure

I get


> a Java error. If you create a new session it seems the "memory leak" is

gone.


>

> I need help to resolve this WITHOUT going to 9.2.0.6. Going to the 9i XDK

is a


> option im fighting for but dont really have the approval yet.

>

> Do you feel from your experience that if you have MANY or LARGE XML files

to


> do that processing outside of the DB or at "OFFPEAK" hours?

>

> Thanks,

> CG