Query Returns ORA-01427: single-row subquery returns more than one row when XMLIndex is Present (Doc ID 2049925.1)

Last updated on APRIL 29, 2020

Applies to:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.