Failure with XML query in 11.2.0.2 that worked in 10.2.0.4 (Doc ID 1344977.1)

Last updated on AUGUST 03, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.2 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Goal


In 11.2.0.2, running the following fails:

SELECT tt_xml.instrumentsXml().EXTRACT('/*').getCLOBVal() element FROM DUAL;

FUNCTION instrumentsXml RETURN XMLType
IS
v_xml sys.xmltype;
BEGIN
SELECT XMLElement ("map",
XMLAGG(
XMLElement("entry",
XMLElement("string", instrumentId),
XMLElement("instrument",
XMLForest(
instrumentId "symbol",
isin "isin",
currencyCode "currency",
meGroupId "instrumentGroup",
meGroupId "matchingEngine"
),
XMLElement("clearingDetails",
XMLAgg(
XMLElement("entry",
XMLElement("string", clearingAgency),
XMLElement("clearingAgency",
XMLElement("venueInstrumentId", venueInstrumentId)
)
)
)
)
)
)
)
) into v_xml
FROM tibex_instrument
NATURAL JOIN tibex_saInstSettleRouting
WHERE instrumentStatus != 3
GROUP BY instrumentID, isin, currencyCode, meGroupId
ORDER BY instrumentName, currencyCode;

RETURN v_xml;
END instrumentsXml;

The error is:

ORDER BY instrumentName, currencyCode
*
ERROR at line 32:
ORA-00907: missing right parenthesis


Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms