0 Replies Latest reply on Jan 25, 2019 10:05 AM by 954788

    generate large xml to file hit ORA-04031, how to break the write process and free up memory?

    954788

      ---------------------------------------------------------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

      PL/SQL Release 11.2.0.4.0 - Production

      "CORE 11.2.0.4.0 Production"

      TNS for Linux: Version 11.2.0.4.0 - Production

      NLSRTL Version 11.2.0.4.0 - Production

      ---------------------------------------------------------------------------------------------------------

      Dear All,

       

      I need help in generating large xml into file from table. Thanks in advance.

      For the error message that I am facing - ORA-04031, I am not allow to increase memory. So, I need a way to break my write process like open file to write some data --> close file , free memory --> re open file to write balance data --> close file to complete the process.

       

      I have 9 cursors. I read somewhere that cursor will eat up memory. I am not sure if that is why I am hit by the not enough memory error.

      Or maybe I have a complex grouping that cause the error?

       

      Please find below my query and the way that I am trying to break the write to file process into smaller chunk.

       

      declare
      fileHandler UTL_FILE.FILE_TYPE;
      v_clob_length INTEGER;
      pos INTEGER := 1;
      buffer1 VARCHAR2(32767);
      amt BINARY_INTEGER := 32760;
      file_open_counter INTEGER := 1;
      
      l_docClob CLOB;
      --document
      l_xmlDoc xmldom.DomDocument;
      l_xmlDocNode xmldom.DomNode;
      --comment
      l_xmlComment xmldom.DomNode;
      --root
      l_xmlRoot xmldom.DomNode;
      --grouping nodes
      ......;
      --text
      l_xmlText xmldom.DomNode;
      --cursor to get data from different tables. One table per cursor
      CURSOR prof IS
      ......;
      --cursor2
      CURSOR jnt1 (p_cif VARCHAR2) IS
      ......;
      --cursor3
      CURSOR jnt2 (p_cif VARCHAR2, p_acct VARCHAR2) IS
      ......;
      --cursor4
      CURSOR trx (p_acct VARCHAR2) IS
      ......;
      --cursor5
      CURSOR asset (p_cif VARCHAR2) IS
      ......;
      --cursor6
      CURSOR subTotal (p_cif VARCHAR2) IS
      ......;
      --cursor7
      CURSOR foot IS
      ......;
      --cursor8
      CURSOR note IS
      ......;
      --cursor9
      CURSOR checksum IS
      ......;
      Begin
      fileHandler := UTL_FILE.FOPEN('TMP', 'QAS.xml', 'W');
      dbms_lob.createtemporary(l_docClob, false);
      --initialise the document
          --create xml document object
          l_xmlDoc := xmldom.newDOMDocument();
          l_xmlDocNode := xmldom.makeNode(doc=>l_xmlDoc);
          --xml declaration
          xmldom.setVersion(l_xmlDoc,'1.0" encoding="WINDOWS-1252');
          xmldom.setCharset(l_xmlDoc,'WINDOWS-1252');
          
          --comment
          l_xmlComment := xmldom.appendChild(n => l_xmlDocNode,
                              newChild => xmldom.makeNode(
                                  com => xmldom.createComment(l_xmlDoc,' Generated by Oracle Reports version 9.0.4.0.33 ')
                              )
                          );
         
          --create root element and convert to node
          l_xmlRoot := xmldom.appendChild(n => l_xmlDocNode,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'QAS')
                          )
                       );
          
          FOR curProf IN prof LOOP
              --create parent element
              ParentNode1 := xmldom.appendChild(n => l_xmlRoot,
                              newChild => xmldom.makeNode(
                                  elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent1TagName')
                              )
                             );
          
              ChildNode1 := xmldom.appendChild(n => ParentNode1,
                              newChild => xmldom.makeNode(
                                  elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child1TagName')
                              )
                             );
                             
                  SubChildNode1 := xmldom.appendChild(n => ChildNode1,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'SubChild1TagName')
                                  )
                                 );
                  --set tag value
                  l_xmlText := xmldom.appendChild(n => SubChildNode1,
                                  newChild => xmldom.makeNode(
                                      t => xmldom.createTextNode(doc => l_xmlDoc, data => curProf.ColumnName)
                                  )
                               );
         ......;
                  
                  ParentNode2 := xmldom.appendChild(n => ChildNode1,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent2TagName')
                                  )
                                 );
                  FOR curJnt1 IN jnt1(curProf.ColumnName) LOOP
                  ParentNode3 := xmldom.appendChild(n => ParentNode2,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent3TagName')
                                  )
                                 );
                      FOR curJnt2 IN jnt2(curJnt1.ColumnName, curJnt1.ColumnName) LOOP
                      ChildNode2 := xmldom.appendChild(n => ParentNode3,
                                      newChild => xmldom.makeNode(
                                          elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child2TagName')
                                      )
                                     );
                       l_xmlText := xmldom.appendChild(n => ChildNode2,
                                      newChild => xmldom.makeNode(
                                          t => xmldom.createTextNode(doc => l_xmlDoc, data => curJnt2.ColumnName)
                                      )
                                   );
                      ......;
                      ParentNode4 := xmldom.appendChild(n => ParentNode3,
                                      newChild => xmldom.makeNode(
                                          elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent4TagName')
                                      )
                                     );
                          FOR curTrx IN trx(curJnt2.ColumnName) LOOP
                          ParentNode5 := xmldom.appendChild(n => ParentNode4,
                                          newChild => xmldom.makeNode(
                                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent5TagName')
                                          )
                                         );
                          ChildNode3 := xmldom.appendChild(n => ParentNode5,
                                          newChild => xmldom.makeNode(
                                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child3TagName')
                                          )
                                         );
                          l_xmlText := xmldom.appendChild(n => ChildNode3,
                                          newChild => xmldom.makeNode(
                                              t => xmldom.createTextNode(doc => l_xmlDoc, data => curTrx.ColumnName)
                                          )
                                       );
                          ......;                                 
                          END LOOP;
                      END LOOP;
                  END LOOP;
                  
                  ParentNode6 := xmldom.appendChild(n => ChildNode1,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent6TagName')
                                  )
                                 );            
                  ParentNode7 := xmldom.appendChild(n => ParentNode6,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent7TagName')
                                  )
                                 );
                                 
                                 --asset
                  ChildNode3 := xmldom.appendChild(n => ParentNode7,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child3TagName')
                                  )
                                 );
                  l_xmlText := xmldom.appendChild(n => ChildNode3,
                                  newChild => xmldom.makeNode(
                                      t => xmldom.createTextNode(doc => l_xmlDoc, data => curProf.ColumnName)
                                  )
                               );
                  ParentNode8 := xmldom.appendChild(n => ParentNode7,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent8TagName')
                                  )
                                 );
                  
                  FOR curAsset IN asset(curProf.ColumnName) LOOP
                      ParentNode9 := xmldom.appendChild(n => ParentNode8,
                                      newChild => xmldom.makeNode(
                                          elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent9TagName')
                                      )
                                     );
          
                          ParentNode10 := xmldom.appendChild(n => ParentNode9,
                                          newChild => xmldom.makeNode(
                                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent10TagName')
                                          )
                                         );
                          l_xmlText := xmldom.appendChild(n => ParentNode10,
                                          newChild => xmldom.makeNode(
                                              t => xmldom.createTextNode(doc => l_xmlDoc, data => curAsset.ColumnName)
                                          )
                                       );
                          ......;                                 
                  END LOOP;
                  
                  FOR curSubTotal IN subTotal(curProf.ColumnName) LOOP
                  ChildNode4 := xmldom.appendChild(n => ChildNode1,
                                  newChild => xmldom.makeNode(
                                      elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child4TagName')
                                  )
                                 );
                  l_xmlText := xmldom.appendChild(n => ChildNode4,
                                  newChild => xmldom.makeNode(
                                      t => xmldom.createTextNode(doc => l_xmlDoc, data => curSubTotal.ColumnName)
                                  )
                               );
                  ......;                         
                  END LOOP;
          END LOOP;
          ParentNode11 := xmldom.appendChild(n => l_xmlRoot,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent11TagName')
                          )
                         );
          
          FOR curFooter IN foot LOOP
          ParentNode12 := xmldom.appendChild(n => ParentNode11,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent12TagName')
                          )
                         );
              ChildNode5 := xmldom.appendChild(n => ParentNode12,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child5TagName')
                          )
                         );
              l_xmlText := xmldom.appendChild(n => ChildNode5,
                              newChild => xmldom.makeNode(
                                  t => xmldom.createTextNode(doc => l_xmlDoc, data => curFooter.ColumnName)
                              )
                           );
              ......;                 
                         
          END LOOP;
          ParentNode13 := xmldom.appendChild(n => l_xmlRoot,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent13TagName')
                          )
                         );
          ParentNode14 := xmldom.appendChild(n => ParentNode13,
                      newChild => xmldom.makeNode(
                          elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Parent14TagName')
                      )
                     );                    
          FOR curNote IN note LOOP
              ChildNode6 := xmldom.appendChild(n => ParentNode14,
                          newChild => xmldom.makeNode(
                              elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child6TagName')
                          )
                         );
              l_xmlText := xmldom.appendChild(n => ChildNode6,
                              newChild => xmldom.makeNode(
                                  t => xmldom.createTextNode(doc => l_xmlDoc, data => curNote.ColumnName)
                              )
                           );
              ......;                     
          END LOOP;
      FOR curCheckSum IN checksum LOOP
          ChildNode7 := xmldom.appendChild(n => l_xmlRoot,
                      newChild => xmldom.makeNode(
                          elem => xmldom.createElement(doc => l_xmlDoc, tagName => 'Child7TagName')
                      )
                     );
          l_xmlText := xmldom.appendChild(n => ChildNode7,
                          newChild => xmldom.makeNode(
                              t => xmldom.createTextNode(doc => l_xmlDoc, data => curCheckSum.ColumnName)
                          )
                       );
          ......;
      /****
      May I know how do I split the clob so that i don't hit by error message:
      ORA-04031: unable to allocate 100392 bytes of shared memory 
      ("large pool","unknown object","QERHJ hash-joi","kllcqc:kllcqslt")
      I tried to free the memory with following codes, but it doesn't do what i intended to do. To split the
      clob into smaller chunk and write to file, break at certain point, close the file,
      free memory, re open the file to append the remaining data in the clob.
      I am thinking that the clob will be free from previous data and will have the
      unfinished data after the break. Am i doing it correctly ?
      ****/
                  --write to file
                  dbms_xmldom.writetoclob( doc => l_xmlDoc, cl => l_docClob );
                  v_clob_length := length(l_docClob);
                  
                  WHILE pos < v_clob_length LOOP
                      dbms_lob.read(l_docClob, amt, pos, buffer1);
                      UTL_FILE.PUTF(fileHandler, buffer1);
                      UTL_FILE.fflush(fileHandler);
                      pos := pos + amt;
                  END LOOP;
                  
                      file_open_counter := file_open_counter +1;
                  IF file_open_counter = 500 THEN
                      COMMIT;
                      UTL_FILE.FCLOSE(fileHandler);
                      file_open_counter := 1;
                      dbms_lob.freetemporary(l_docClob);
                      --re open file to write balance data
          fileHandler := UTL_FILE.FOPEN('TMP', 'QAS.xml', 'A');
                      dbms_lob.createtemporary(l_docClob, false);
                  END IF;
      END LOOP;
      --release memory
          UTL_FILE.FCLOSE(fileHandler);
          dbms_session.free_unused_user_memory;
          dbms_lob.freetemporary(l_docClob);
          xmldom.freeDocument(l_xmlDoc);
       COMMIT;
      EXCEPTION
          WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
          UTL_FILE.FCLOSE_ALL;    
      End;
      /