XMLLAGG Function Giving Different Results After Upgrading 11g To 12c (Doc ID 2687129.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On : 12.2.0.1 version, XML Database

ACTUAL BEHAVIOR
---------------
XMLLAGG function giving different results after upgrading 11g to 12c

Here is a testcase

sqlplus des@persia

SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 27 22:35:07 2020

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning and Automatic Storage Management options

SQL> set long 150
SQL> create table INC000004017693 (id number, xml sys.xmltype);

Table created.

SQL> insert into INC000004017693 (id, xml) values (1,'<TEAM>eq1</EQUIPMENT>');

1 row created.

SQL> insert into INC000004017693 (id, xml) values (2,'<TEAM>eq2</TEAM>');

1 row created.

SQL> select XMLAGG(xml).GETCLOBVAL() from INC000004017693;

XMLAGG(XML).GETCLOBVAL()
--------------------------------------------------------------------------------
<TEAM>eq1</TEAM>
<TEAM>eq2</TEAM>

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Solaris: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> drop table INC000004017693;

Table dropped.

SQL> exit
------------------------------------------------------------------------

sqlplus des@persia

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 27 22:38:49 2020

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> set long 150
SQL> create table INC000004017693 (id number, xml sys.xmltype);

Table created.

SQL> insert into INC000004017693 (id, xml) values (1,'<TEAM>eq1</EQUIPMENT>');

1 row created.

SQL> insert into INC000004017693 (id, xml) values (2,'<TEAM>eq2</TEAM>');

1 row created.

SQL> select XMLAGG(xml).GETCLOBVAL() from INC000004017693;

XMLAGG(XML).GETCLOBVAL()
--------------------------------------------------------------------------------
<TEAM>eq1</TEAM><TEAM>eq2</TEAM>

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
CON_ID
----------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
0

PL/SQL Release 12.2.0.1.0 - Production
0

CORE 12.2.0.1.0 Production
0

TNS for Linux: Version 12.2.0.1.0 - Production
0

NLSRTL Version 12.2.0.1.0 - Production
0

SQL> drop table INC000004017693;

Table dropped.

SQL> exit

Doing the same thing, in an 11g the XMLAGG puts each sys.xmltype record in a different line, in a 12c it puts them in the same line
That's the problem.

Changes

Cause

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