8 Replies Latest reply: Feb 11, 2013 5:50 AM by 381244 RSS

    XML Generation changed behavior in Oracle 11.2.0.3.0 Version

    Sohil Bhavsar
      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
          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
            Sohil Bhavsar
            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
              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
                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
                  Sohil Bhavsar
                  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
                    Sohil Bhavsar
                    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.
                    • 8. Re: XML Generation changed behavior in Oracle 11.2.0.3.0 Version
                      381244
                      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