Hello
I have two functions in a PL/SQL package. When we receive an xml file from the client, I have two methods to convert the file to a LOB and then save it a column of type shown below: If method 1 fails, tries the method 2.
Method 1
v_lob := DBMS_XSLPROCESSOR.read2clob(p_xmldir_in, v_filename_with_type, nls_charset_id('AL32UTF8')); -- not sure what this charset is??
Method 2
v_file := BFILENAME(p_xmldir_in,v_filename_with_type);
DBMS_LOB.createtemporary(v_lob, TRUE);
DBMS_LOB.fileopen(v_file);
DBMS_LOB.loadfromfile(v_lob, v_file, DBMS_LOB.getlength(v_file));
DBMS_LOB.fileclose(v_file);
p_lob := v_lob;
DBMS_LOB.freetemporary(v_lob);
Then save it into the column:
XML_PAYLOAD NOT NULL SYS.XMLTYPE(XMLSchema "capac
ity-document.xsd" Element "COracle Database 10g Enterprise Edition Release 10.1.0.5.0
apacityDocument") STORAGE Ob
ject-relational TYPE "Capaci
tyDocument172_T"
Problem: Both methods fails in Oracle 11g.
A file submitted by client to our production environment loads successfully. This is running Oracle Database 10g Enterprise Edition Release 10.1.0.5.0. OpenVMS Alpha.
However, on a migration box (OpenVMS - Itanium), running Oracle Database 11g Enterprise Edition Release 11.2.0.4.0, presenting the same file, it is failing:
Error:
58751628 SPC_AEL_MAIN 20-aug-2018 08:07 FATAL
Oracle error -20012 in function While calling package pkg_spc_xml: ORA-20012: OR
A-20012: ORA-22993: specified input amount is greater than actual source amount
ORA-19007: Schema - does not match expected capacity-document.xsd. in func p_loa
d_xml_file_meth2 in func p_load_ic_capacity_xml
ORA-06512: at "SPICE.PKG_SPC_XML", line 6256
ORA-06512: at line 1 , function p_load_ic_capacity_xml.
Any suggestions?
Regards
James