使用PL/SQL结果将大型XML打印到ORA-10260:事件10261设置的PGA堆的限制大小(1048576)
使用PL/SQL结果将大型XML打印到ORA-10260:事件10261设置的PGA堆的限制大小(1048576)
提问于 2020-06-27 15:07:03
我们目前正在从下面的大型查询中用XML编写一大段代码：
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;当从select语句检索到的数据集很小(当使用request_id := 206时)时，输出如下所示：
<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>但是，当我们有更大的数据集(使用request_id := 233)时，它会运行很长时间，文件大小超过1GB。对于更大的数据集，它运行了很长一段时间，并在以下方面出错：
ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded以下是我所面临的限制和我迄今所采取的步骤：
Use dbms_xmldom.writetofile without a Named Directory)
- I
- I不能使用dbms_xmldom.writetofile或UTL_FILE包，因为它需要一个指定的目录(请参阅：只能使用DBMS_OUTPUT或FND_FILE来写入输出。
- ，我已经确认了程序运行的时间长度不是因为查询，但这与输出文件的编写有关。
- I不能更改PGA设置。
- I不能使用SQL*Plus。
- I也不能创建另一个程序来对文件执行假脱机操作。
是否有优化文件写入方式的方法？
回答 1
Stack Overflow用户
发布于 2020-06-29 09:36:18
为了缩进XML，我使用这个过程，也许它可以解决您的问题。
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;当您有非常大的XML文件时，最好是“逐行”创建它们，而不是一个单一的对象，即将XML分割成较小的部分并输出它们。当然，您必须付出一定的努力来确保最终得到一个格式良好的XML，但原则上它的大小是无限的。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/62611697
复制相关文章
相似问题
腾讯云开发者
