SQL & PL/SQL

Post code, most people on these forums refuse to download files included zips.

What are you trying to achieve exactly?  Can you use XMLTable / XMLQuery to parse the doc instead?

Could be...

Problem (memory leak using DBMS_XMLPARSER):

Unpublished Bug:8918821 – MEMORY LEAK IN DBMS_XMLPARSER IN QMXDPLS_SUBHEAP closed as “not a bug”. The problem is caused by the fact that the XML document is created with XMLDOM.CREATEELEMENT, but after creation XMLDOM.FREEDOCUMENT is not called. This causes the XML used heaps to remain allocated. Every new call to XMLDOM.CREATEELEMENT will then allocate a new heap, causing process memory to grow over time, and hence cause the ORA-4030 error to occur in the end.

Solution:

To implement a solution for this issue, use XMLDOM.FREEDOCUMENT to explicitly free any explicitly or implictly created XML document, so the memory associated with that document can be released for reuse.

@Hi Paulzip

Thanks for your post.

I have a PL/SQL application that uses the dbms_xmlparser and xlst processor to unpack a CLOB field holding XML (originally from an HL7 message).  It would need a major rewrite to not use the dbms_xmlparser and xslprocessor functions.

I am

Here are snippets of the pl/sql code showing what I am trying to do-EPDSDEV databasePL/SQL code

Calling routine (snippets)....

..

cursor c1 is select  xml,              seqno      from    hl7.xml_from_tie_deploy      where  status = 'UNPROCESSED'      and rownum < 1000      order by seqno;

begin

open c1; fetch c1 into l_clob, l_seqno;....while not c1%notfound loop

-- Create a parser.

      l_parser := dbms_xmlparser.newParser;

-- Parse the document and create a new DOM document.

      dbms_xmlparser.parseClob(l_parser, l_clob);
  
     -- Free any resources associated with the document before it is used
      dbms_xmldom.freeDocument(l_doc);
  
      l_doc := dbms_xmlparser.getDocument(l_parser);

-- Free resources associated with the CLOB and Parser now they are no longer needed.
     dbms_xmlparser.freeParser(l_parser);

..

l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),l_rootname||'/MSH');

inMSH IN dbms_xmldom.DOMNode,   -- inMSH is l_nl

..

dbms_xslprocessor.valueOf(inMSH,'MSH.1/text()',l_fieldsep); -- fieldsep   This is the function which sometimes retrieves a null value

..

      -- Free any resources associated with the document now it      -- is no longer needed.     

dbms_xmldom.freeDocument(l_doc);

<<fetch_next>>fetch c1 into l_clob, l_seqno;

end loop

Whilst I understand you say it's a big rewrite, the approach you are using is very inefficient.  You are loading the full DOM into memory, a full parse tree into memory, yet you may only be reading a few XPath values.  That's where XMLTable excels, it doesn't do that if you are using absolute XPath.

Try moving the contents of your code into a sub procedure,  If it is some memory leak bug, scoping might fix it - however I suspect the problem is with your code.  You should also handle freeing of resources on exceptions.  Your code isn't very robust or modularised to be honest, here's my version of your code....

declare

  cursor c1 is

    select  xml, seqno    

    from    hl7.xml_from_tie_deploy    

    where  status = 'UNPROCESSED' and rownum < 1000    

    order by seqno;

  procedure ProcessXML(pClob clob) is

    l_parser dbms_xmlparser.Parser;

    l_doc dbms_xmldom.DOMDocument;

    procedure FreeResources is

    begin

      if l_doc is not null then

        dbms_xmldom.freeDocument(l_doc);

        l_doc := null;      -- Just in case there is garbage collection leaks

      end if;

      if l_parser is not null then

        dbms_xmlparser.freeParser(l_parser);

        l_parser := null;

      end if;

    end;

  begin

-- 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);

    l_nl := dbms_xslprocessor.selectNodes(dbms_xmldom.makeNode(l_doc),l_rootname||'/MSH');

    --inMSH := .....       NOT SURE WHAT YOU ARE DOING HERE

    dbms_xslprocessor.valueOf(inMSH,'MSH.1/text()',l_fieldsep); -- fieldsep   This is the function which sometimes retrieves a null value

          -- Free any resources associated with the document now it      -- is no longer needed.    

    FreeResources;

  exception

    when OTHERS then

      FreeResources;

      raise;  

  end;

begin

  open c1;

  loop

    fetch c1 into l_clob, l_seqno;

    exit when not c1%notfound;

    ProcessXML(l_clob);

  end loop;

  close c1;

end;

I have a PL/SQL application that uses the dbms_xmlparser and xlst processor to unpack a CLOB field holding XML (originally from an HL7 message). It would need a major rewrite to not use the dbms_xmlparser and xslprocessor functions. 

All you need to parse a CLOB to XMLTYPE is to call  XMLTYPE(l_clob) and then use XMLQUERY or XMLTABLE to extract the data

Hi Paulzip and cormaco

Many thanks for your tips. I will look into using XMLTABLE. Can I use XMLQUERY and XMLTABLE against the same XMLTYPE object? Which is more efficient? I need to do some sophisticated XML querying to unpack repeating nodes/segments - would I need to use XMLQUERY to do this?

Mark S NHS wrote:
Hi Paulzip and cormaco
Many thanks for your tips. I will look into using XMLTABLE. Can I use XMLQUERY and XMLTABLE against the same XMLTYPE object? Which is more efficient? I need to do some sophisticated XML querying to unpack repeating nodes/segments - would I need to use XMLQUERY to do this?

Yes you can.

As a general rules, the difference is XMLQuery returns query results as XML, XMLTable returns results as relation data.

In your situation with unpacking node sequences into relational data, I'd recommend XMLTable.