We are hitting below error when we try to insert version and encoding to xmltype variable, using XMLROOT
in our plsql code:
ORA-10260: limit size (1048576) of the PGA heap set by event 10261 exceeded
DIAGNOSTIC ANALYSIS:
--------------------
db version: 12.1.0.2.0
NLS_CHARACTERSET AL32UTF8 0
NLS_NCHAR_CHARACTERSET AL16UTF16 0
Charset used in xml file: UTF-8
Code snippet:
--Insert version and UTF-8 Encoding to generated xml data
SELECT XMLROOT(v_xml, version '1.0" encoding="UTF-8') INTO v_xmlUTF8Encoding FROM dual;
--where v_xml and v_xmlUTF8Encoding are of xmltype.
After we insert the new element, we convert the xmltype to xmlblob:
v_op_schema_rec.xmlblob := v_xmlUTF8Encoding.getBlobval(873);
This error is occurring after we introduced UTF-8 encoding in the beginning of the xml file using XMLROOT. We added this to avoid xml parsing error that we faced while using chinese characters in the data.
After we added UTF-8 encoding in the xml data, we got rid of xml parsing error of the generated xml file. But we are getting PGA error now. The same data on another Env where we have the same code, does not throw PGA
error. Has this got to do with any db settings? Please let us know.
The data in v_xml is about 1711365 records. The generated xml file size where there was no PGA error is, 362MB