Query Returns ORA-01427: single-row subquery returns more than one row when XMLIndex is Present
(Doc ID 2049925.1)
Last updated on APRIL 12, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.2 and laterInformation in this document applies to any platform.
Symptoms
Table is created as follows -
CREATE TABLE "xxxx"."XXXX_GENERIC_DATA"
( "DATA_CODE" VARCHAR2(20 BYTE) PRIMARY KEY,
"PUBLICATION_DATE" DATE NOT NULL ENABLE,
"DATA_XML" "SYS"."XMLTYPE"
) XMLTYPE XML_DATA STORE AS BINARY XML;
Insert couple of SDMX 2.1 generic data files into this table, one is 30MB the other is 1.7GB, e.g. -
INSERT INTO XXXX_GENERIC_DATA VALUES ('une_rt_m', SYSDATE, XMLType(bfilename('DATA', 'une_rt_m_data.xml'), nls_charset_id('AL32UTF8')));
INSERT INTO XXXX_GENERIC_DATA VALUES ('demo_r_mlife', SYSDATE, XMLType(bfilename('DATA', 'demo_r_mlife_data.xml'), nls_charset_id('AL32UTF8')));
COMMIT;
Create a XML index -
CREATE INDEX gen_data_xmlindex ON XXXX_GENERIC_DATA(DATA_XML) indextype IS xdb.xmlindex
parameters ('PATHS (INCLUDE (
/message:GenericDatGenericData/message:DataSet/generic:Series/generic:SeriesKey/generic:Value)
NAMESPACE MAPPING (xmlns:generic="http://www.XXXX.org/resources/sdmxml/schemas/v2_1/data/generic"
xmlns:message="http://www.XXXX.org/resources/sdmxml/schemas/v2_1/message"))');
Try to query for distinct values from a particular XPATH using an XMLTABLE as below:
SELECT DISTINCT DATA_CODE, KEY_VALUE
FROM XXXX_GENERIC_DATA D,
XMLTable (
XMLNAMESPACES(
'http://www.XXXX.org/resources/sdmxml/schemas/v2_1/message' AS "msg",
'http://www.XXXX.org/resources/sdmxml/schemas/v2_1/data/generic' AS "gen"
),
'/msg:GenericData/msg:DataSet/gen:Series/gen:SeriesKey/gen:Value' passing D.DATA_XML
COLUMNS
KEY_VALUE VARCHAR2(256) PATH './@id'
) as xt;
returns below Oracle error:
ORA-01427: single-row subquery returns more than
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 |