EXTRACT Function Over XMLAGG Returns Null Values In Oracle 11.2.0.1 (Doc ID 1371172.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.1 - Release: 11.2 to 11.2
Information in this document applies to any platform.

Symptoms

The function EXTRACT(XMLAGG(XMLELEMENT)) returns null when
cursor_sharing=force in Oracle Server 11.2.0.1.
It works fine when cursor_sharing=exact or similar, and both in 11.2.0.2 and 11.2.0.3

Example of query:
SELECT npswire_oid, npswire_name, NPSWIRE_type ,
LTRIM (EXTRACT ( XMLAGG ( XMLELEMENT ( "m",','|| NVL(mp_name,'N/A') ) ORDER BY mp_name),'/m/text()'), ',' ) mapnames
FROM dummy
GROUP BY npswire_oid ,
npswire_name,npswire_TYPE;

In 11.2.0.2 the result is:
NPSWIRE_OID                   NPSWIRE_NAME         NPSWIRE_TYPE    MAPNAMES
----------------------------- -------------------- --------------- --------
{[NetworkPseudowire(Id=28)]} 777175@c4-npe1-76 --  Ethernet Tagged training
                             777175@c7-npe1-76

{[NetworkPseudowire(Id=43)]} 483783@c4-npe1-76 -- Ethernet Tagged h
                             483783@c7-npe1-76

{[NetworkPseudowire(Id=268)]} 777098@c1-npe1-76 -- Ethernet Tagged Lena
                              777098@c2-npe1-crs

3 rows selected.

In 11.2.0.1 with cursor_sharing=force the result is:
NPSWIRE_OID                   NPSWIRE_NAME         NPSWIRE_TYPE    MAPNAMES
----------------------------- -------------------- --------------- --------
{[NetworkPseudowire(Id=28)]}  777175@c4-npe1-76 -- Ethernet Tagged
                              777175@c7-npe1-76

{[NetworkPseudowire(Id=43)]} 483783@c4-npe1-76 -- Ethernet Tagged
                              483783@c7-npe1-76

{[NetworkPseudowire(Id=268)]} 777098@c1-npe1-76 -- Ethernet Tagged
                              777098@c2-npe1-crs

3 rows selected.

Cause

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