This discussion is archived
8 Replies Latest reply: Feb 11, 2013 3:50 AM by 381244 RSS

XML Generation changed behavior in Oracle 11.2.0.3.0 Version

SohilBhavsar Newbie
Currently Being Moderated
Hi,

We have been facing a strange issue with Oracle 11.2.0.3.0 version.

The following query is working fine on Oracle 11.2.0.2.0 version:
SELECT XMLAGG(     XMLELEMENT("DEPT", 
        XMLFOREST(D.DEPTNO, 
          (SELECT XMLAGG( 
            XMLELEMENT("EMP",
              XMLFOREST(E.EMPNO "EMPNO")) ORDER BY E.ENAME) 
            FROM EMP E WHERE E.JOB='MANAGER')  "EMP_LIST" )
        ) ORDER BY D.DEPTNO  ) AS DE
  FROM DEPT D WHERE DEPTNO=10;
it gives following output:
<DEPT>
     <DEPTNO>10</DEPTNO>
     <EMP_LIST>
          <EMP>
               <EMPNO>7698</EMPNO>
          </EMP>
          <EMP>
               <EMPNO>7782</EMPNO>
          </EMP>
          <EMP>
               <EMPNO>7566</EMPNO>
          </EMP>
     </EMP_LIST>
</DEPT>
When we run same query on Oracle 11.2.0.3.0 it was hanging current Oracle session, to resolve this we applied patch 13477790 and after that we have been able to run the query but it will give different result and will not include <EMP_LIST> tag.
<DEPT>
     <DEPTNO>10</DEPTNO>
     <EMP>
          <EMPNO>7698</EMPNO>
     </EMP>
     <EMP>
          <EMPNO>7782</EMPNO>
     </EMP>
     <EMP>
          <EMPNO>7566</EMPNO>
     </EMP>
</DEPT>
Then we applied another patch 14727310 for latest release of Oracle 11.2.0.3.5 but still no luck.

Is it a bug in Oracle 11.2.0.3.0 or a changed behavior?

Regards,

Sohil Bhavsar
  • 1. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    odie_63 Guru
    Currently Being Moderated
    Hi,

    I can reproduce the behaviour on both versions :

    11.2.0.2 :
    Connected to:
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
    
    SQL> set long 500
    SQL> SELECT XMLSerialize(content
      2           XMLAGG(
      3             XMLELEMENT("DEPT",
      4               XMLFOREST(D.DEPTNO,
      5                 (
      6                   SELECT XMLAGG(
      7                            XMLELEMENT("EMP",
      8                              XMLFOREST(E.EMPNO "EMPNO")
      9                            )
     10                            ORDER BY E.ENAME
     11                          )
     12                   FROM scott.EMP E WHERE E.JOB = 'MANAGER'
     13                 )  "EMP_LIST"
     14               )
     15             )
     16             ORDER BY D.DEPTNO
     17           )
     18           indent
     19         ) AS DE
     20  FROM scott.DEPT D
     21  WHERE DEPTNO=10 ;
    
    DE
    --------------------------------------------------------------------------------
    <DEPT>
      <DEPTNO>10</DEPTNO>
      <EMP_LIST>
        <EMP>
          <EMPNO>7698</EMPNO>
        </EMP>
        <EMP>
          <EMPNO>7782</EMPNO>
        </EMP>
        <EMP>
          <EMPNO>7566</EMPNO>
        </EMP>
      </EMP_LIST>
    </DEPT>
    11.2.0.3 :
    SQL> SELECT XMLAGG(
      2           XMLELEMENT("DEPT",
      3             XMLFOREST(D.DEPTNO,
      4               (
      5                 SELECT XMLAGG(
      6                          XMLELEMENT("EMP",
      7                            XMLFOREST(E.EMPNO "EMPNO")
      8                          )
      9                          ORDER BY E.ENAME
     10                        )
     11                 FROM scott.EMP E
     12                 WHERE E.JOB = 'MANAGER'
     13               )  "EMP_LIST"
     14             )
     15           )
     16           ORDER BY D.DEPTNO
     17         ) AS DE
     18  FROM scott.DEPT D
     19  WHERE DEPTNO = 10 ;
             XMLELEMENT("DEPT",
                         *
    ERROR at line 2:
    ORA-03113: end-of-file on communication channel
    Process ID: 5060
    Session ID: 133 Serial number: 25
    I don't have MOS access rigth now, are those two patches supposed to address this exact issue?

    There's a simple workaround that doesn't need any patch though, use XMLElement instead of XMLForest.
    I'd even say it's preferable in the general case because it's more flexible :
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    PL/SQL Release 11.2.0.3.0 - Production
    CORE    11.2.0.3.0      Production
    TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
    NLSRTL Version 11.2.0.3.0 - Production
    
    SQL> set long 500
    SQL> set pages 100
    SQL> SELECT XMLSerialize(content
      2           XMLAGG(
      3             XMLELEMENT("DEPT",
      4               XMLELEMENT("DEPTNO", D.DEPTNO)
      5             , XMLELEMENT("EMP_LIST",
      6                 (
      7                   SELECT XMLAGG(
      8                            XMLELEMENT("EMP",
      9                              XMLFOREST(E.EMPNO "EMPNO")
     10                            )
     11                            ORDER BY E.ENAME
     12                          )
     13                   FROM scott.EMP E
     14                   WHERE E.JOB = 'MANAGER'
     15                 )  "EMP_LIST"
     16               )
     17             )
     18             ORDER BY D.DEPTNO
     19           )
     20           indent
     21         ) AS DE
     22  FROM scott.DEPT D
     23  WHERE DEPTNO = 10 ;
    
    DE
    --------------------------------------------------------------------------------
    <DEPT>
      <DEPTNO>10</DEPTNO>
      <EMP_LIST>
        <EMP>
          <EMPNO>7698</EMPNO>
        </EMP>
        <EMP>
          <EMPNO>7782</EMPNO>
        </EMP>
        <EMP>
          <EMPNO>7566</EMPNO>
        </EMP>
      </EMP_LIST>
    </DEPT>
    Edited by: odie_63 on 25 janv. 2013 12:53
  • 2. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    SohilBhavsar Newbie
    Currently Being Moderated
    Hi odie,

    You can resolve following error by patch 13477790.
    ERROR at line 2:
    ORA-03113: end-of-file on communication channel
    Process ID: 5060
    Session ID: 133 Serial number: 25
    Regarding use of XMLElement:

    The query which I had put for dept and emp table are just sample.

    Our original query has lots of columns, that is why we are using XMLForest to get some flexibility.

    If we follow your suggestion then we need to change those code. :-(


    Regards,

    Sohil Bhavsar.
  • 3. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    odie_63 Guru
    Currently Being Moderated
    The query which I had put for dept and emp table are just sample.
    Yes, thought so ;)
    Our original query has lots of columns, that is why we are using XMLForest to get some flexibility.
    "flexibility" is not the right term. I'd say more like "easy coding".
    XMLForest is only a shorthand for this :
    CASE WHEN expr IS NOT NULL THEN
      XMLElement("myTag", expr)
    END
  • 4. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    AlexAnd Guru
    Currently Being Moderated
    using WITH
    SQL> select * from v$version where rownum=1;
     
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
     
    SQL> 
    SQL> SELECT XMLAGG(     XMLELEMENT("DEPT",
      2          XMLFOREST(D.DEPTNO,
      3            (with t as  --add
      4            (SELECT XMLAGG(
      5              XMLELEMENT("EMP",
      6                XMLFOREST(E.EMPNO "EMPNO")) ORDER BY E.ENAME)
      7                as qwe --add
      8              FROM scott.EMP E WHERE E.JOB='MANAGER')
      9              select qwe from t)--add
     10             "EMP_LIST" )
     11          ) ORDER BY D.DEPTNO  ) AS DE
     12    FROM scott.DEPT D WHERE DEPTNO=10;
     
    DE
    --------------------------------------------------------------------------------
    <DEPT><DEPTNO>10</DEPTNO><EMP_LIST><EMP><EMPNO>7698</EMPNO></EMP><EMP><EMPNO>778
     
    SQL> ---or
    SQL> 
    SQL> 
    SQL> SELECT XMLAGG(     XMLELEMENT("DEPT",
      2          XMLFOREST(D.DEPTNO,
      3            (with t(qwe) as  --add
      4            (SELECT XMLAGG(
      5              XMLELEMENT("EMP",
      6                XMLFOREST(E.EMPNO "EMPNO")) ORDER BY E.ENAME)
      7              FROM scott.EMP E WHERE E.JOB='MANAGER')
      8              select qwe from t)--add
      9             "EMP_LIST" )
     10          ) ORDER BY D.DEPTNO  ) AS DE
     11    FROM scott.DEPT D WHERE DEPTNO=10;
     
    DE
    --------------------------------------------------------------------------------
    <DEPT><DEPTNO>10</DEPTNO><EMP_LIST><EMP><EMPNO>7698</EMPNO></EMP><EMP><EMPNO>778
     
    SQL> 
    but i prefer XMLElement

    Edited by: AlexAnd on Jan 25, 2013 5:57 AM
  • 5. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    SohilBhavsar Newbie
    Currently Being Moderated
    Thanks Alex and odie.

    Do you have any idea whether it is a bug or changed behavior in 11.2.0.3.0?

    Is there any way alternative by which we can revert to old XML Parser?

    Regards,

    Sohil Bhavsar.
  • 6. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    SohilBhavsar Newbie
    Currently Being Moderated
    Hi odie and Alex,

    Applied patch 13941541 on top of Oracle Server 11.2.0.3.0 + patch 13477790.

    Now got the correct result in Oracle 11.2.0.3.0

    Thanks,

    Sohil Bhavsar.
  • 7. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    AlexAnd Guru
    Currently Being Moderated
    thanks for update
  • 8. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
    381244 Newbie
    Currently Being Moderated
    This query gives inconsistent behaviour even in version 11.2.0.2:

    SQL> SELECT XMLRoot(
    2 XMLELEMENT (
    3 "DEP",
    4 XMLAGG (
    5 XMLELEMENT (
    6 "DEPDATA",
    7 XMLFOREST (
    8 d.deptno ID,
    9 d.dname DEPNAME,
    10 d.loc LOCATION,
    11 (SELECT XMLAGG (
    12 XMLELEMENT (
    13 "EMPDATA",
    14 XMLFOREST (e.empno, e.ename EMPNAME, e.job JOB))
    15 ORDER BY e.ename)
    16 FROM scott.emp e
    17 WHERE d.deptno = e.deptno) "EMP_LIST"))
    18 ORDER BY d.dname))
    19 , VERSION '1.0', STANDALONE YES).getClobVal() as XMLDATA
    20 FROM SCOTT.DEPT d;

    XMLDATA
    --------------------------------------------------------------------------------
    <?xml version="1.0" standalone="yes"?>
    <DEP>
    <DEPDATA>
    <ID>10</ID>
    <DEPNAME>ACCOUNTING</DEPNAME>
    <LOCATION>NEW YORK</LOCATION>
    <EMP_LIST>
    <EMPDATA>
    <EMPNO>7782</EMPNO>
    <EMPNAME>CLARK</EMPNAME>
    <JOB>MANAGER</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7839</EMPNO>
    <EMPNAME>KING</EMPNAME>
    <JOB>PRESIDENT</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7934</EMPNO>
    <EMPNAME>MILLER</EMPNAME>
    <JOB>CLERK</JOB>
    </EMPDATA>
    </EMP_LIST>
    </DEPDATA>
    <DEPDATA>
    <ID>40</ID>
    <DEPNAME>OPERATIONS</DEPNAME>
    <LOCATION>BOSTON</LOCATION>
    </DEPDATA>
    <DEPDATA>
    <ID>20</ID>
    <DEPNAME>RESEARCH</DEPNAME>
    <LOCATION>DALLAS</LOCATION>
    <EMPDATA>
    <EMPNO>7876</EMPNO>
    <EMPNAME>ADAMS</EMPNAME>
    <JOB>CLERK</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7902</EMPNO>
    <EMPNAME>FORD</EMPNAME>
    <JOB>ANALYST</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7566</EMPNO>
    <EMPNAME>JONES</EMPNAME>
    <JOB>MANAGER</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7788</EMPNO>
    <EMPNAME>SCOTT</EMPNAME>
    <JOB>ANALYST</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7369</EMPNO>
    <EMPNAME>SMITH</EMPNAME>
    <JOB>CLERK</JOB>
    </EMPDATA>
    </DEPDATA>
    <DEPDATA>
    <ID>30</ID>
    <DEPNAME>SALES</DEPNAME>
    <LOCATION>CHICAGO</LOCATION>
    <EMP_LIST>
    <EMPDATA>
    <EMPNO>7499</EMPNO>
    <EMPNAME>ALLEN</EMPNAME>
    <JOB>SALESMAN</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7698</EMPNO>
    <EMPNAME>BLAKE</EMPNAME>
    <JOB>MANAGER</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7900</EMPNO>
    <EMPNAME>JAMES</EMPNAME>
    <JOB>CLERK</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7654</EMPNO>
    <EMPNAME>MARTIN</EMPNAME>
    <JOB>SALESMAN</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7844</EMPNO>
    <EMPNAME>TURNER</EMPNAME>
    <JOB>SALESMAN</JOB>
    </EMPDATA>
    <EMPDATA>
    <EMPNO>7521</EMPNO>
    <EMPNAME>WARD</EMPNAME>
    <JOB>SALESMAN</JOB>
    </EMPDATA>
    </EMP_LIST>
    </DEPDATA>
    </DEP>

    EMP_LIST container is not present for department 20.

    Moreover, if you refine the query adding "where d.deptno in (20, 40)", and you change the set, you get different behavior (sometimes dept 20, sometimes dept 30).

    Connecting to patched db 11.2.0.3 + 13477790 , no tag is shown.

    The problem is that with version 11.2.0.2 the behaviour is already buggy...

    Edited by: uquaia on Feb 11, 2013 12:49 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points