SQL & PL/SQL

Hi:

I'm posting this in the SQL forum even though the bulk of the procedure is using the DBMS_XML related packages.

I get the error mentioned in the title when trying to transform a large XML source iteratively. What I'm doing is getting a small (a few K) XML fragment in a clob nd repeatedly passing it to a procedure to transform that XML into another format of XML. I'm writing the results of each "chunk" out to a table as a append it to a column that is being updated (pretty standard).

My problem is that I can do so many iterations before running out of memory so I'm guessing I have a leak somewhere but I don't know where. I can get about 2,000 iterations (resulting in a 170M clob) but I would think I should be able to get multiples of that if I don't have a leak. I even run out of memory if I don't append the results to the updated column, so the leak has to do with the CLOBs I'm working with I guess. I'd appreciate anyone pointing out what I am forgetting to free here.

I've seen references about increasing the PGA_AGGREGATE_TARGET to fix this problem. I've done that but it didn't help. In any case, I shouldn't hit this problem at all if I'm releasing memory properly.

I've included the code below.

Thanks

CREATE OR REPLACE PACKAGE GAFF.FOO_PCK AS

PROCEDURE TransformXMLClob(fragment IN CLOB, xsldoc IN CLOB, transformedFragment IN OUT NOCOPY CLOB) ;
PROCEDURE TEST;
END FOO_PCK;

The package body

CREATE OR REPLACE PACKAGE BODY GAFF.FOO_PCK AS


-- fragment - Clob containing an XML fragment (it isn't an XML document)
-- xsldoc   - Clob containing the XSL stylesheet to use in the transformation
-- transformedFragment - Clob that contains the transformed XML fragment.

PROCEDURE transformXMLClob(fragment IN CLOB, xsldoc IN CLOB,
                            transformedFragment IN OUT NOCOPY CLOB) IS 
 
   indoc        CLOB := empty_clob;    -- XMLFragament to transform, CLOB from
   
   myParser     DBMS_XMLPARSER.Parser;
   indomdoc     DBMS_XMLDOM.domdocument;
   xsltdomdoc   DBMS_XMLDOM.domdocument;
   xsl          DBMS_XSLPROCESSOR.stylesheet;
   outdomdocf   DBMS_XMLDOM.domdocumentfragment;
   outnode      DBMS_XMLDOM.domnode;
   proc         DBMS_XSLPROCESSOR.processor;
   buf          CLOB := empty_clob;
   i            NUMBER;
   
   rootBeginTag CLOB := '<?xml version="1.0" encoding="UTF-8"?><root>';
   rootEndTag   CLOB := '</root>';

BEGIN


DBMS_LOB.createTemporary (indoc, TRUE);

    if( DBMS_LOB.isopen(indoc) != 1) then
       dbms_lob.open( indoc, dbms_lob.lob_readwrite );
    end if ;

   indoc := rootBeginTag || fragment || rootEndTag;


   DBMS_LOB.createTemporary (buf, TRUE);

   myParser := DBMS_XMLPARSER.newParser;
   DBMS_XMLPARSER.parseClob (myParser, indoc);
   indomdoc := DBMS_XMLPARSER.getDocument (myParser);

   -- Don't transform here.  Feed one node at a time to parser later.
   DBMS_XMLPARSER.parseClob (myParser, xsldoc);
   xsltdomdoc := DBMS_XMLPARSER.getDocument (myParser);
   xsl := DBMS_XSLPROCESSOR.newstylesheet (xsltdomdoc, '');
   proc := DBMS_XSLPROCESSOR.newProcessor;

   --apply stylesheet to DOM document

   outdomdocf := DBMS_XSLPROCESSOR.processxsl (proc, xsl, indomdoc);
   outnode := DBMS_XMLDOM.makenode (outdomdocf);

   -- PL/SQL DOM API for XMLType can be used here

   DBMS_XMLDOM.writetoClob (outnode, buf);
   --buf := '<?xml version="1.0" encoding="UTF-8"?>' || buf;
   
   transformedFragment := buf;

   --dbms_output.put_line(substr(buf,1,3000));

   DBMS_XMLPARSER.freeParser (myParser);
   DBMS_XSLPROCESSOR.freeProcessor (proc);

   if( DBMS_LOB.isopen(indoc) = 1) then
       dbms_lob.close( indoc);
    end if ; 
   DBMS_LOB.FreeTemporary (indoc);
   
 
   
   if( DBMS_LOB.isopen(buf) = 1) then
       dbms_lob.close( buf);
    end if ;
   DBMS_LOB.FreeTemporary (buf); 
   
   
   if( DBMS_LOB.isopen(rootBeginTag) = 1) then
       dbms_lob.close(rootBeginTag);
    end if ;
   DBMS_LOB.FreeTemporary (rootBeginTag); 

   
   if( DBMS_LOB.isopen(rootEndTag) = 1) then
       dbms_lob.close(rootEndTag);
    end if ;
   DBMS_LOB.FreeTemporary (rootEndTag);       


END transformXMLClob;


PROCEDURE TEST IS
   xsl             CLOB ; -- := empty_Clob();
   indoc1          CLOB ; -- := empty_Clob();
   indoc2          CLOB ; -- := empty_Clob();
   transformedFragment CLOB;
   transformedDocument CLOB;
   tmpClob         CLOB;
   
   i               INTEGER;
   fragmentStart   INTEGER;
   fragmentEnd     INTEGER;

   numNodes        INTEGER;
   checkEnd        varchar2(1000);
   
BEGIN
   -- Get the stylesheet.
   SELECT   col1 INTO xsl FROM xsl_tab2;
   
   SELECT   file_content
     INTO   indoc1
     FROM   xml_data_template
    WHERE   my_key = 15364;
    
   fragmentStart := instr(indoc1,'<root ');
   fragmentEnd := instr(indoc1,'</root>');


   indoc2 := substr(indoc1,fragmentStart,(length(indoc1) - 
                    (fragmentStart + length('</root>'))) );
                    
 
   insert into xml_data_template
          ( my_key,
            file_path,
            file_type,
            file_content )
   values ( 1000,
               'big/clob/test',
               'EXPORT',
               EMPTY_CLOB() );
   commit;

   SELECT file_content 
   INTO transformedDocument 
   FROM xml_data_template
   WHERE my_key = 1000 FOR UPDATE;

   DBMS_LOB.OPEN (transformedDocument,DBMS_LOB.LOB_READWRITE);
              
   /* SCV2 docs are enclosed in a <RecordSet/> element.  */
   
   DBMS_LOB.append(transformedDocument,'<?xml version="1.0" encoding="UTF-8"?>' );
   
   
   for i in 1..5000 LOOP
   
     FOO_PCK.TransformXMLClob(indoc2, xsl, transformedFragment);
        DBMS_LOB.append(transformedDocument,transformedFragment);
   
     
    if( DBMS_LOB.isopen(transformedFragment) = 1) then
       dbms_lob.close( transformedFragment);
    end if ;
    
   DBMS_LOB.FreeTemporary (transformedFragment); 

   --transformedFragment := empty_clob;
   end LOOP;
   

   tmpClob := '</RecordSet>';
   --transformedFragment := tmpClob;
   
   --dbms_lob.append(transformedDocument, tmpClob);
           
    if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
      DBMS_LOB.CLOSE ( transformedDocument );
    end if; 
  
    if (DBMS_LOB.ISOPEN ( tmpClob ) = 1 ) then
      DBMS_LOB.CLOSE ( tmpClob );
    end if; 

    if (DBMS_LOB.ISOPEN ( indoc1 ) = 1 ) then
      DBMS_LOB.CLOSE ( indoc1 );
    end if; 

    if (DBMS_LOB.ISOPEN ( indoc2 ) = 1 ) then
      DBMS_LOB.CLOSE ( indoc2 );
    end if; 
    
    if (DBMS_LOB.ISOPEN ( xsl ) = 1 ) then
      DBMS_LOB.CLOSE ( xsl );
    end if; 
              
   COMMIT;
   
   tmpClob := empty_clob;
   
   if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
      DBMS_LOB.CLOSE ( transformedDocument );
   end if;
   
   
   EXCEPTION
           WHEN OTHERS THEN
           dbms_output.put_line('EXCEPTION!' || SQLERRM);
            if (DBMS_LOB.ISOPEN ( transformedDocument ) = 1 ) then
              DBMS_LOB.CLOSE ( transformedDocument );
            end if; 
           commit;
   
END TEST;

END FOO_PCK;
/