XMLTABLE Query Fails With ORA-19279: XPTY0004 After Upgrade To 19c
(Doc ID 2774577.1)
Last updated on JULY 20, 2024
Applies to:
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and laterInformation in this document applies to any platform.
Symptoms
After 19c upgrade getting error ORA-19279
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence
Query :
SELECT xt.fflp AS fflp,
xt.fflp_crdr AS fflp_crdr
FROM DVP_RTTM.EVENT_MESSAGE_HIST em,
XMLTABLE (
XMLNAMESPACES ('http://dvp.dtcc.com/EventMessage' AS "msg"),
'/msg:message'
PASSING em.event
COLUMNS sec_id VARCHAR2 (9)
PATH 'body/securitySummary/detail/secId',
payment_date VARCHAR2 (32)
PATH 'body/securitySummary/detail/paymentDate',
price VARCHAR2 (32)
PATH 'body/securitySummary/detail/price',
coupon_rate VARCHAR2 (32)
PATH 'body/securitySummary/detail/coupon',
coupon_indicator VARCHAR2 (32)
PATH 'body/securitySummary/detail/coupon/@status',
fndp VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FNDP"]/amount',
fndp_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FNDP"]/amount/@type',
fflp VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFLP"]/amount',
fflp_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFLP"]/amount/@type',
ffmk VARCHAR2 (32)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFMK"]/amount',
ffmk_crdr VARCHAR2 (6)
PATH 'body/securitySummary/detail/lineItems/lineItem[fundsType="FFMK"]/amount/@type',
fcpn VARCHAR2 (32)
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Cause |
Solution |
References |