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:
- 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)
- I can only use DBMS_OUTPUT or FND_FILE to write to the output.
- 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.
- I cannot change the PGA settings.
- I can’t use SQL*Plus.
- 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?
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.