Oracle version : 11.2.0
Snippet of XML file :
<pmt:Single>
<pmt:SingSequence>0001</pmt:SingSequence>
<pmt:SingType>
<cmn:TranCode>CTX</cmn:TranCode>
<cmn:TranTypeDesc>ACH</cmn:TranTypeDesc>
</pmt:SingType>
<pmt:EntryDate>
<cmn:DTInd>Date</cmn:DTInd>
<cmn:DTDate>2015-05-26</cmn:DTDate>
</pmt:EntryDate>
<pmt:PmtDate>
<cmn:DTInd>Date</cmn:DTInd>
<cmn:DTDate>2015-02-10</cmn:DTDate>
</pmt:PmtDate>
<pmt:PmtAmount>
<cmn:AmtType>Payment Amount</cmn:AmtType>
<cmn:Amt>
<cmn:MonAmtType>Monetary Amount</cmn:MonAmtType>
<cmn:MonAmtCrDbFlag>C</cmn:MonAmtCrDbFlag>
<cmn:AmtVal>34500</cmn:AmtVal>
</cmn:Amt>
</pmt:PmtAmount>
Error Code:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TRANSACTION.BBVA_SP_INSERT_ACHPAYMENT", line 1517
ORA-19114: XPST0003 - error during parsing the XQuery expression:
LPX-00801: XQuery syntax error at 'EOF'
1 XMLSchema-instance";/bbva:bbva/bbva:Pmt/pmt:Payments/pmt:Single/
- ^
ORA-06512: at line 11
Process exited.
Disconnecting from the database transaction-DEV.
Procedure Code:
CURSOR c_single_info
IS
SELECT single_info_d.ext_ref,
single_info_d.app_seq_id,
single_info_d.proprietary,
single_info_d.add_info_table,
single_info_d.entry_date,
single_info_d.pmt_date,
single_info_d.crdb_flag,
single_info_d.pmt_amt_curr,
single_info_d.amount,
single_info_d.exch_rate,
single_info_d.exch_rate_code
FROM xmltable(
xmlnamespaces(
'http://www.xxxxx.com/Canonical' AS "bbva",
'http:/xxxxx.com/PmtInfo' AS "pmt",
'http://xxxxx.com/CommonTypes' AS "cmn",
'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
'/xxxxx:xxxxx/xxxxx:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single'
PASSING payXml
COLUMNS single_info XMLTYPE PATH '/pmt:Single') single_info_t,
xmltable(
xmlnamespaces(
'http://www.xxxxxx.com/Canonical' AS "bbva",
'http://xxxxxx.com/PmtInfo' AS "pmt",
'http://xxxxxx.com/CommonTypes' AS "cmn",
'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
'/pmt:Single'
PASSING single_info_t.single_info
COLUMNS ext_ref varchar2(256)
PATH '/pmt:SingExtRef',
app_seq_id varchar2(256)
PATH '/pmt:SingSequence',
proprietary varchar2(256)
PATH '/pmt:SingType/cmn:TranCode',
add_info_table varchar2(256)
PATH '/pmt:SingType/cmn:TranTypeDesc',
entry_date date
PATH '/pmt:EntryDate/cmn:DTDate',
pmt_date date
PATH '/pmt:PmtDate/cmn:DTDate',
crdb_flag varchar2(256)
PATH '/pmt:PmtAmount/cmn:Amt/cmn:MonAmtCrDbFlag',
pmt_amt_curr varchar2(256)
PATH '/pmt:PmtAmount/cmn:Amt/cmn:Currency',
amount number(18,3)
PATH '/pmt:PmtAmount/cmn:Amt/cmn:AmtVal',
exch_rate number(20,12)
PATH '/pmt:PmtAmount/cmn:Exchange/cmn:ExchRate',
exch_rate_code varchar2(256)
PATH '/pmt:PmtAmount/cmn:Exchange/cmn:ExchRateCode') single_info_d;
Cannot seem to figure out why the xquery error is being caused. Can someone help please? Thank you in advance...