My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

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

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