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('' AS "xmlns:xsi"                         ,    '' AS "xmlns:tns"                         ,    '' as "xmlns:etd"                         ,    '' 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="" xmlns:tns="" xmlns:etd="" xsi:schemaLocation="">   <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?

Add Comment
1 Answer(s)

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="">'||             '<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 on July 17, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.