• Ask a Question
  • Create a Poll
    Ask a Question
    More answer You can create 5 answer(s).
      Ask a Poll

      Printing Large XML using PL/SQL results into ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded

      We are currently writing a large piece of code in XML from a large query like below:

      set serveroutput on; declare          lc_xml          xmltype;     --g_request_id  number := 233;     g_request_id    number := 206;     g_subsection    varchar2(10) := 'ALL';     g_affname       varchar2(10) := 'AFF';     g_number_format VARCHAR2(30) := 'FM999999999999999999999990D00';          c_blob          clob;     v_offset        number := 1;     v_chunk_size    number := 32767;     l_length        NUMBER := 0;  begin      WITH     gljournal as     (         SELECT  context_request_id             ,case                 when INFO_CHAR45 is null then null                 else INFO_CHAR45||' '||INFO_NUMBER14             end jrnl_RepSeqName_Number             ,   REGEXP_REPLACE(INFO_CHAR32,'[^[a-z,A-Z,0-9,chr(0)-chr(127)[:space:]]]*','') INFO_CHAR32             ,case                 when INFO_CHAR46 is null then null                 else INFO_CHAR46 ||' '|| INFO_NUMBER15             end Subledger_Doc_Number             ,   INFO_CHAR33             ,   INFO_DATE4             ,   INFO_DATE2             ,   INFO_DATE3             ,   INFO_CHAR39             ,   sum((nvl(info_number4, 0) - nvl(info_number11, 0))) tot_jrnl_amt         from    xx_xml_table         where   section_code = 'GL_JOURNALS'         and     sub_section_code in ('MANUAL', 'SUBLEDGER')         and     context_request_id = g_request_id         group by                 context_request_id             ,   INFO_CHAR45             ,   INFO_NUMBER14             ,   INFO_CHAR32             ,   INFO_CHAR46             ,   INFO_NUMBER15             ,   INFO_CHAR33             ,   INFO_DATE4             ,   INFO_DATE2             ,   INFO_DATE3             ,   INFO_CHAR39     )     SELECT  XMLElement("tns:JPK"             ,   XMLATTRIBUTES('http://www.w3.org/2001/XMLSchema-instance' AS "xmlns:xsi"                         ,    'http://jpk.mf.gov.pl/wzor/2016/03/09/03091/' AS "xmlns:tns"                         ,    'http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2016/01/25/eD/DefinicjeTypy/' as "xmlns:etd"                         ,    'http://jpk.mf.gov.pl/wzor/2016/03/09/03091/Schemat_JPK_KR(1)_v1-0.xsd' as "xsi:schemaLocation")             ,   XMLELEMENT("tns:Naglowek"                         ,   XMLELEMENT("tns:KodFormularza", XMLATTRIBUTES(SUB_SECTION_CODE AS "kodSystemowy", INFO_CHAR1 AS "wersjaSchemy"), 'JPK_KR')                         ,   XMLELEMENT("tns:WariantFormularza", INFO_CHAR2)                         ,   XMLELEMENT("tns:CelZlozenia", INFO_CHAR2)                         ,   XMLELEMENT("tns:DataWytworzeniaJPK", INFO_CHAR3)                         ,   XMLELEMENT("tns:DataOd", INFO_DATE1)                         ,   XMLELEMENT("tns:DataDo", INFO_DATE2)                         ,   XMLELEMENT("tns:DomyslnyKodWaluty", INFO_CHAR6)                         ,   XMLELEMENT("tns:KodUrzedu", INFO_CHAR7))             ,   XMLELEMENT("tns:Podmiot1"                 ,   XMLELEMENT("tns:IdentyfikatorPodmiotu"                     ,   XMLELEMENT("etd:NIP", INFO_CHAR8)                     ,   XMLELEMENT("etd:PelnaNazwa", INFO_CHAR4)                     ,   XMLELEMENT("etd:REGON", INFO_CHAR5))                 ,   XMLELEMENT("tns:AdresPodmiotu"                     ,   XMLELEMENT("etd:KodKraju", INFO_CHAR9)      -- country                     ,   XMLELEMENT("etd:Wojewodztwo", INFO_CHAR10)  -- state                     ,   XMLELEMENT("etd:Powiat", INFO_CHAR11)       -- county                     ,   XMLELEMENT("etd:Gmina", INFO_CHAR12)        -- province                     ,   XMLELEMENT("etd:Ulica", INFO_CHAR13)        -- address1                     ,   XMLELEMENT("etd:NrDomu", INFO_CHAR14)       -- building                     --, case when INFO_CHAR15 is not null then                     --  XMLELEMENT("etd:NrLokalu", INFO_CHAR15) end -- floor_number                     ,   XMLELEMENT("etd:Miejscowosc", INFO_CHAR16)  -- City                     ,   XMLELEMENT("etd:KodPocztowy", INFO_CHAR17)  -- Postal Code                     ,   XMLELEMENT("etd:Poczta", INFO_CHAR18)))     -- address2         ,   CASE WHEN g_subsection IN ('ALL', 'TRIAL_BALANCE') THEN             (select XMLAGG(XMLCONCAT(XMLELEMENT("tns:ZOiS", XMLATTRIBUTES('G' AS "typ")                 ,   XMLELEMENT("tns:KodKonta", DECODE(g_affname,'AFF', info_char31, info_char49))                       -- Account, AccountingFlexField                 ,   XMLELEMENT("tns:OpisKonta", info_char32)                                                        -- AccountDescription                 ,   XMLELEMENT("tns:TypKonta", info_char35)                                                         -- AccountType                 ,   XMLELEMENT("tns:KodZespolu", SUBSTR(info_char34, -1))                                           -- ACCT_LEVEL2_VALUE                 ,   XMLELEMENT("tns:OpisZespolu", info_char35)                                                      -- AccountType                 ,   XMLELEMENT("tns:KodKategorii", SUBSTR(info_char33, -3))                                         -- ACCT_LEVEL3_VALUE                 ,   XMLELEMENT("tns:OpisKategorii", info_char32)                                                    -- AccountDescription                 ,   XMLELEMENT("tns:KodPodkategorii", info_char31)                                                  -- Account                 ,   XMLELEMENT("tns:OpisPodkategorii", info_char32)                                                 -- AccountDescription                 ,   XMLELEMENT("tns:BilansOtwarciaWinien", to_char(sum(NVL(info_number3,0)),g_number_format))       -- OpeningDebitBalance                 ,   XMLELEMENT("tns:BilansOtwarciaMa", to_char(sum(NVL(info_number4,0)),g_number_format))           -- OpeningCreditBalance                 ,   XMLELEMENT("tns:ObrotyWinien", to_char(sum(NVL(info_number5,0)),g_number_format))               -- Period_Debit_Balance                 ,   XMLELEMENT("tns:ObrotyMa", to_char(sum(NVL(info_number6,0)),g_number_format))                   -- Period_Credit_Balance                 ,   XMLELEMENT("tns:ObrotyWinienNarast", to_char(sum(NVL(info_number8,0)),g_number_format))         -- ClosingDebitBalance                 ,   XMLELEMENT("tns:ObrotyMaNarast", to_char(sum(NVL(info_number9,0)),g_number_format))             -- ClosingCreditBalance                 ,   XMLELEMENT("tns:SaldoWinien", to_char(sum(NVL(info_number8,0)),g_number_format))                -- ClosingDebitBalance                 ,   XMLELEMENT("tns:SaldoMa", to_char(sum(NVL(info_number9,0)),g_number_format))                    -- ClosingCreditBalance                 )))             from    xx_xml_table             where   section_code = 'GL_ACCOUNTS'             and     sub_section_code = 'BALANCES'             and     context_request_id = g_request_id             and     info_number7 <> 0        -- Exclude Accounts with No Balances             group by                     info_char31                 ,   info_char35                 ,   info_char34                 ,   info_char33                 ,   info_char32                 ,   info_char49)             END         -- Manual and Subledger Journals         ,   CASE WHEN g_subsection IN ('ALL', 'GL_JOURNALS') THEN             (SELECT XMLAGG(XMLCONCAT(XMLELEMENT("tns:Dziennik", XMLATTRIBUTES('G' AS "typ")                 ,   XMLELEMENT("tns:LpZapisuDziennika", rownum)                                     -- rownum                 ,   XMLELEMENT("tns:NrZapisuDziennika", jrnl_RepSeqName_Number)                     -- Journal : Reporting Sequence Name + Number                 ,   XMLELEMENT("tns:OpisDziennika", INFO_CHAR32)                                    -- Description of the Journal                 ,   XMLELEMENT("tns:NrDowoduKsiegowego", Subledger_Doc_Number)                      -- Subledger Document Number                 ,   XMLELEMENT("tns:RodzajDowodu", INFO_CHAR33)                                     -- Source document type to which line relates                 ,   XMLELEMENT("tns:DataOperacji", INFO_DATE4)                                      -- Actual Sales Date or Purchase Date                 ,   XMLELEMENT("tns:DataDowodu", INFO_DATE2)                                        -- Journal creation date.                 ,   XMLELEMENT("tns:DataKsiegowania", INFO_DATE3)                                   -- Journal Date                 ,   XMLELEMENT("tns:KodOperatora", INFO_CHAR39)                                     -- User name / ID of the person who created                 ,   XMLELEMENT("tns:OpisOperacji", INFO_CHAR32)                                     -- Description of the transaction                 ,   XMLELEMENT("tns:DziennikKwotaOperacji",to_char(tot_jrnl_amt,g_number_format))   -- Journal Total Amount                 )))             from    gljournal)             END         -- Manual Journal Summary         ,   CASE WHEN g_subsection IN ('ALL', 'GL_JOURNALS') THEN             (SELECT XMLELEMENT("tns:DziennikCtrl"                 ,   XMLELEMENT("tns:LiczbaWierszyDziennika", COUNT(context_request_id)) -- Count                 ,   XMLELEMENT("tns:SumaKwotOperacji", to_char(SUM(tot_jrnl_amt), g_number_format)) -- Sum                 )             from    gljournal             group by context_request_id)             END         -- Subledger Journal         ,   CASE WHEN g_subsection IN ('ALL', 'ACCOUNT_ANALYSIS') THEN             (SELECT XMLAGG(XMLCONCAT(XMLELEMENT("tns:KontoZapis", XMLATTRIBUTES('G' AS "typ")                 ,   XMLELEMENT("tns:LpZapisu", rownum)                 ,   XMLELEMENT("tns:NrZapisu", INFO_CHAR45 || ' ' || INFO_NUMBER7)              --Journal : Reporting Sequence Name + Number                 ,   XMLELEMENT("tns:KodKontaWinien",                      DECODE(g_affname,'AFF', INFO_CHAR31, INFO_CHAR49))                          --Debit Account ID                 ,   XMLELEMENT("tns:KwotaWinien", to_char(INFO_NUMBER3, g_number_format))       --Amount of transaction on the debit side                 ,   XMLELEMENT("tns:KwotaWinienWaluta", to_char(INFO_NUMBER1, g_number_format)) --Currency Amount of transaction on the debit side entry                 ,   XMLELEMENT("tns:KodWalutyWinien", INFO_CHAR35)                              --Currency Code for the debit side entry                 ,   XMLFOREST(INFO_CHAR34 as "tns:OpisZapisuWinien")                            --Description of the transaction on the debit side                 ,   XMLELEMENT("tns:KodKontaMa",                                                     DECODE(g_affname,'AFF', INFO_CHAR31, INFO_CHAR49))                          --Credit Account ID                 ,   XMLELEMENT("tns:KwotaMa", to_char(info_number4, g_number_format))           --Amount of transaction on the credit side                 ,   XMLELEMENT("tns:KwotaMaWaluta", to_char(INFO_NUMBER2, g_number_format))     --Currency Amount of transaction on the credit side entry                 ,   XMLELEMENT("tns:KodWalutyMa", INFO_CHAR35)                      --Currency Code for the credit side entry                 ,   XMLFOREST(INFO_CHAR34 as "tns:OpisZapisuMa"))                   --Description of the transaction on the credit side                 ))             from    xx_xml_table acct             where   1=1             and     section_code = 'GL_JOURNALS'             and     sub_section_code in ('MANUAL', 'SUBLEDGER')             and     context_request_id = g_request_id             )             END         -- Subledger Journal Summary         ,   CASE WHEN g_subsection IN ('ALL', 'ACCOUNT_ANALYSIS') THEN             (select XMLELEMENT("tns:KontoZapisCtrl"                 ,   XMLELEMENT("tns:LiczbaWierszyKontoZapisj", count(context_request_id))                 ,   XMLELEMENT("tns:SumaWinien", to_char(sum(nvl(info_number3, 0)), g_number_format))                 ,   XMLELEMENT("tns:SumaMa", to_char(sum(nvl(info_number4, 0)), g_number_format)))             from    xx_xml_table             where   1=1             and     section_code = 'GL_JOURNALS'             and     sub_section_code in ('MANUAL', 'SUBLEDGER')             and     context_request_id = g_request_id             group by context_request_id)             END         )     into    lc_xml     FROM    xx_xml_table     WHERE   SECTION_CODE = 'SH'     and     context_request_id = g_request_id;          -- i previously used this but it wasn't pretty printed     -- c_blob := lc_xml.getclobval();      -- i used XMLSERIALIZE here so that it will be pretty printed     SELECT  XMLSERIALIZE( DOCUMENT lc_xml AS CLOB INDENT SIZE=2 )     into    c_blob     from    dual;          DBMS_OUTPUT.ENABLE (buffer_size => NULL);          l_length := dbms_lob.getlength( c_blob );          loop     exit when v_offset > l_length;                  DBMS_OUTPUT.PUT_LINE(dbms_lob.substr( c_blob, v_chunk_size, v_offset ));         v_offset := v_offset + v_chunk_size;     end loop;      EXCEPTION     WHEN NO_DATA_FOUND THEN         DBMS_OUTPUT.PUT_LINE('No Data Found!'); END; 

      This works when the dataset retrieved from the select statement is small (when using request_id := 206), the output is like below:

      <tns:JPK xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tns="http://jpk.mf.gov.pl/wzor/2016/03/09/03091/" xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2016/01/25/eD/DefinicjeTypy/" xsi:schemaLocation="http://jpk.mf.gov.pl/wzor/2016/03/09/03091/Schemat_JPK_KR(1)_v1-0.xsd">   <tns:Naglowek>     <tns:KodFormularza kodSystemowy="JPK_KR (1)" wersjaSchemy="1-0">JPK_KR</tns:KodFormularza>     <tns:WariantFormularza>1</tns:WariantFormularza>     <tns:CelZlozenia>1</tns:CelZlozenia>     <tns:DataWytworzeniaJPK>2020-06-24T08:16:21Z</tns:DataWytworzeniaJPK>     <tns:DataOd>2005-01-01</tns:DataOd>     <tns:DataDo>2005-01-31</tns:DataDo>     <tns:DomyslnyKodWaluty>PLN</tns:DomyslnyKodWaluty>     <tns:KodUrzedu>0202</tns:KodUrzedu>   </tns:Naglowek>   <tns:Podmiot1>     <tns:IdentyfikatorPodmiotu>       <etd:NIP>5270201492</etd:NIP>       <etd:PelnaNazwa>Vision Poland Report</etd:PelnaNazwa>       <etd:REGON>SYSREGON12134</etd:REGON>     </tns:IdentyfikatorPodmiotu>     <tns:AdresPodmiotu>       <etd:KodKraju>PL</etd:KodKraju>       <etd:Wojewodztwo>mazowieckie</etd:Wojewodztwo>       <etd:Powiat>Cunty-warsaw</etd:Powiat>       <etd:Gmina>Prov-warsaw</etd:Gmina>       <etd:Ulica>1Maja</etd:Ulica>       <etd:NrDomu>7</etd:NrDomu>       <etd:Miejscowosc>Warsaw</etd:Miejscowosc>       <etd:KodPocztowy>01-607</etd:KodPocztowy>       <etd:Poczta>Post-warsaw-1</etd:Poczta>     </tns:AdresPodmiotu>   </tns:Podmiot1> </tns:JPK> 

      However, when we have larger datasets (using request_id := 233), it runs for a very long time and the file size is over 1 GB. For even larger datasets, it runs for a very long time and it errors out with:

      ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded             

      Below are the following limitations I am faced with and the steps i have taken so far:

      1. I cannot use dbms_xmldom.writetofile or UTL_FILE packages because it needs a named directory (see: Use dbms_xmldom.writetofile without a Named Directory)
      2. I can only use DBMS_OUTPUT or FND_FILE to write to the output.
      3. I have confirmed the length of time the program runs is not because of the query, but it’s with the writing of the output file.
      4. I cannot change the PGA settings.
      5. I can’t use SQL*Plus.
      6. I also can’t create another program to do the spooling to a file.

      Is there a way to optimize how the file is being written?

      1 Answers

      In order to indent an XML I use this procedure, perhaps it could solve your problem.

      PROCEDURE MakePrettyXml(xmlString IN OUT NOCOPY CLOB) IS      xmlDocFragment DBMS_XMLDOM.DOMDOCUMENTFRAGMENT;     xslProc DBMS_XSLPROCESSOR.PROCESSOR;     xsl DBMS_XSLPROCESSOR.STYLESHEET;     xmlStringOut CLOB;      BEGIN      DBMS_LOB.CREATETEMPORARY(xmlStringOut, TRUE);     xslProc := DBMS_XSLPROCESSOR.NEWPROCESSOR;      xsl := DBMS_XSLPROCESSOR.NEWSTYLESHEET(         '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">'||             '<xsl:output method="xml" indent="yes"/>'||             '<xsl:template match="@*|node( )">'||                 '<xsl:copy>'||                     '<xsl:apply-templates select="@*|node( )"/>'||                 '</xsl:copy>'||             '</xsl:template>'||         '</xsl:stylesheet>', NULL);      xmlDocFragment := DBMS_XSLPROCESSOR.PROCESSXSL(p => xslProc, ss => xsl, cl => xmlString);     DBMS_XMLDOM.WRITETOCLOB(DBMS_XMLDOM.MAKENODE(xmlDocFragment), xmlStringOut);              DBMS_XSLPROCESSOR.FREESTYLESHEET(xsl);     DBMS_XSLPROCESSOR.FREEPROCESSOR(xslProc);              xmlString := xmlStringOut;      DBMS_LOB.FREETEMPORARY(xmlStringOut);          END MakePrettyXml; 

      When you have very big XML files then it might be better to create them "line-by-line" instead of one single object, i.e. cut the XML into smaller pieces and output them. Of course, you have to spend some effort to ensure a well-formed XML at the end but the size is unlimited in principle.

      Answered by Jcphilipjoni on July 17, 2020..