9 Replies Latest reply: May 8, 2013 12:41 AM by JackK RSS

    Issue with simple query   (it hangs)

    JackK
      Hi,
      Do you know about some problems in Oracle 11g with XML selects? The following query worked very well in Oracle 10g but now in 11g it hangs:
      SELECT XMLElement("QQ_QR",
                          (SELECT XMLAgg(XMLElement("ROW",XMLForest(QQ_BLOCK_NAME, QR_MODULE,QR_NAME,QR_DESC,QR_SQL,
                                      (SELECT XMLAgg(XMLElement("ROW",XMLForest(QC_NAME,QC_DESC,QC_DESC2)))
                                         FROM ODG_QUERY_COLUMNS
                                        WHERE qc_qr_id = qr_id) as "QC")
                                         )
                                  )
                             FROM ODG_QG_QR
                             JOIN ODG_QUERY_REPOSITORY ON qr_id = qq_qr_id
                            WHERE qq_qg_id = 2)
               )--.extract('/*')
          FROM dual;
      My schema is as follows:
       CREATE TABLE "ODG_QG_QR"
        (
          "QQ_QG_ID" NUMBER NOT NULL ENABLE, "QQ_QR_ID" NUMBER NOT NULL ENABLE, "QQ_BLOCK_NAME" VARCHAR2 (30 BYTE) NOT NULL
          ENABLE
        );
      
       CREATE TABLE "ODG_QUERY_GROUPS"
        (
          "QG_ID"                                   NUMBER NOT NULL ENABLE, "QG_MODULE" VARCHAR2 (30 BYTE) NOT NULL ENABLE, "QG_NAME" VARCHAR2 (255 BYTE) NOT
          NULL ENABLE                   , "QG_DESC" VARCHAR2 (300 BYTE)
        );
      
       CREATE TABLE "ODG_QUERY_REPOSITORY"
        (
          "QR_ID"                                                                                   NUMBER NOT NULL ENABLE, "QR_MODULE" VARCHAR2 (30 BYTE) NOT NULL ENABLE, "QR_NAME" VARCHAR2 (255 BYTE) NOT
          NULL ENABLE                   , "QR_SQL" CLOB NOT NULL ENABLE                 , "QR_DESC" VARCHAR2 (300 BYTE)
        );
      
       CREATE TABLE "ODGADM"."ODG_QUERY_COLUMNS"
        (
          "QC_ID"                                           NUMBER (14, 0) NOT NULL ENABLE, "QC_QR_ID" NUMBER (10, 0) NOT NULL ENABLE, "QC_NAME" VARCHAR2 (32 BYTE) NOT
          NULL ENABLE                           , "QC_DESC" VARCHAR2 (100 BYTE)                                                      ,
          "QC_DESC2"                                        VARCHAR2 (1000 BYTE)
        );
      
      REM INSERTING into ODG_QUERY_REPOSITORY
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (17,'T','$$$_TEST','NEED explain');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (5,'GOP','PIS_DEKL','Zapytanie z danymi deklaracji');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (43,'NZ','PIS_PIS',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (44,'NZ','PIS_POW_DOK',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (45,'NZ','DOK_TPL',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (46,'NZ','DOK_TPL_ZAP',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (47,'NZ','PIS_DOK',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (33,'GOP','PIS_NIER_POW','Wywozy z punktów odbiorów dla nieruchomości');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (6,'GOP','PIS_DEKL_DEKN','Zapytanie z danymi nieruchomości do deklaracji');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (9,'GOP','PIS_PIS','Ogóle infomracje o piśmie.');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (40,'GOP','PIS_RF_ZEZW','Infromacje o firmie wywozowej - rejestr zezwoleń na wywóz');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (12,'GOPrzemysłowy','Pismo',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (41,'GOP','PIS_RF_ODP','Infromacje o firmie wywozowej - kody odpadów');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (50,'NZ','PIS_KL',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (31,'GOP','NK_NIER_NK','Klienci do nieruchomości');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (32,'GOP','PIS_NIER_PO_POJ','Pojemniki na nieruchomości');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (34,'GOP','PIS_OBK_KLOB','Klienci podłączeni do obiektu - wszyscy');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (35,'GOP','PIS_OBK','Dane z obiektu');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (36,'GOP','PIS_OBK_KLOB_PLAT','Klient obiektu będący płatnikiem');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (13,'GOP','PIS_NIER_PO','Punkty odbioru na nieruchomości');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (38,'GOP','PIS_RF','Infromacje o firmie wywozowej - ogólne');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (11,'GOP','PIS_NIER','Nieruchomości  (pisma powiązane z nieruchomością)');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (37,'GOP','PIS_OBK_NIER','NIeruchomości związane z obiektami');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (39,'GOP','PIS_RF_RFW','Infromacje o firmie wywozowej - rejestr firm wywozowych');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (7,'GOP','PIS_DEKL_DOBL','Obliczenia dla deklaracji (miesięczne)');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (8,'GOP','PIS_DEKL_DEKN_DPOJ','Zapytanie z pojemnikami dla nieruchomości (Uwaga !!! wymaga bloku nadrzędnego DEKN)');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (1,'EGZ','PIS_1_TW','Pisma z pierwszym tytułem');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (2,'EGZ','TW_DLA_PIS',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (3,'EGZ','DANE_OE',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (4,'EGZ','ZAJ_DLA_PIS',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (16,'GOPrzemysłowy','__TEST__','NO NEED to explain');
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (49,'NZ','PIS_TW',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (51,'NZ','TW_KL_ZOB',null);
      Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (52,'NZ','DOK_KL',null);
      
      REM INSERTING into ODG_QG_QR
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (1,1,'PIS');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (2,5,'DEKL');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (1,2,'TW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (1,3,'OE');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,11,'NIER');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (12,9,'PIS');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,9,'PIS');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,31,'NK');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (22,44,'DOK');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,13,'PO');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (22,46,'TPL_ZAP');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,32,'POJ');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (14,34,'KLOB');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (13,33,'POW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (14,35,'OBK');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (14,37,'NIER');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (14,36,'KLOB_PL');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (1,4,'ZAJ');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (23,49,'TW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (23,50,'KL');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (24,50,'KL');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (24,51,'TW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (24,52,'DOK');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (12,41,'ODP');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (12,38,'RF');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (14,9,'PIS');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (12,39,'RFW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (12,40,'ZEZW');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (2,6,'DEKN');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (2,7,'DOBL');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (2,8,'DEKN_DPOJ');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (3,9,'PIS');
      Insert into ODG_QG_QR (QQ_QG_ID,QQ_QR_ID,QQ_BLOCK_NAME) values (22,45,'TPL');
      
      REM INSERTING into ODG_QUERY_GROUPS
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (1,'EGZ','PIS',null);
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (2,'GOP','PIS_DEKL','Pisma - decyzje / deklaracje');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (6,'T','Testing...',null);
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (22,'NZ','PIS_DOK','Pisma dla dokumentu.');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (23,'NZ','PIS_TW','Pisma dla tytułu wykonawczego');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (24,'NZ','PIS_KL','Pisma dla klienta');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (12,'GOP','PIS_RF','Pisma - rejestr firm wywozowych');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (13,'GOP','PIS_NIER','Pisma - Nieruchomość');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (14,'GOP','PIS_OBK','Pisma obiekty');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (25,'NZ','pis _dok1','opis...1111');
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (3,'GOP','PIS',null);
      Insert into ODG_QUERY_GROUPS (QG_ID,QG_MODULE,QG_NAME,QG_DESC) values (5,'GOPrzemysłowy','Pismo',null);
      
      commit;
      If I comment the subquery aliased as "QC" then it does not hang. Help me, please...
        • 1. Re: Issue with simple query   (it hangs)
          AlexAnd
          thanks for test case

          one moment
          >
          CREATE TABLE "ODG_QUERY_REPOSITORY"
          (
          "QR_ID" NUMBER NOT NULL ENABLE,
          "QR_MODULE" VARCHAR2 (30 BYTE) NOT NULL ENABLE,
          "QR_NAME" VARCHAR2 (255 BYTE) NOT NULL ENABLE,
          "QR_SQL" CLOB NOT NULL ENABLE,
          "QR_DESC" VARCHAR2 (300 BYTE)
          );
          >

          and
          >
          Insert into ODG_QUERY_REPOSITORY (QR_ID,QR_MODULE,QR_NAME,QR_DESC) values (17,'T','$$$_TEST','NEED explain');
          >
          but
          "QR_SQL" CLOB NOT NULL ENABLE,
          ok
          checking without QR_SQL column in ODG_QUERY_REPOSITORY as
          create table ODG_QUERY_REPOSITORY
          (
            QR_ID     NUMBER not null,
            QR_MODULE VARCHAR2(30) not null,
            QR_NAME   VARCHAR2(255) not null,
            QR_DESC   VARCHAR2(300)
          )
          so
          Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 
          Connected as scott
           
          SQL> 
          SQL> SELECT XMLElement("QQ_QR",
            2                      (SELECT XMLAgg(XMLElement("ROW",XMLForest(QQ_BLOCK_NAME, QR_MODULE,QR_NAME,QR_DESC,--QR_SQL,
            3                                  (SELECT XMLAgg(XMLElement("ROW",XMLForest(QC_NAME,QC_DESC,QC_DESC2)))
            4                                     FROM ODG_QUERY_COLUMNS
            5                                    WHERE qc_qr_id = qr_id) as "QC")
            6                                     )
            7                              )
            8                         FROM ODG_QG_QR
            9                         JOIN ODG_QUERY_REPOSITORY ON qr_id = qq_qr_id
           10                        WHERE qq_qg_id = 2)
           11           )--.extract('/*')
           12      FROM dual;
           
          XMLELEMENT("QQ_QR",(SELECTXMLA
          --------------------------------------------------------------------------------
          <QQ_QR><ROW><QQ_BLOCK_NAME>DEKL</QQ_BLOCK_NAME><QR_MODULE>GOP</QR_MODULE><QR_NAM
           
          SQL> 
          and so
          Do you know about some problems in Oracle 11g with XML selects?
          which problem?
          • 2. Re: Issue with simple query   (it hangs)
            JackK
            Sorry. I didn't see that SQL Developer's export to clipboard does not include CLOB column values. In my database QR_SQL column is populated. You can put anything to it for testing purpose.
            Do you know about some problems in Oracle 11g with XML selects?
            which problem?
            I don't realy know. I'm asking because of my problem.
            • 3. Re: Issue with simple query   (it hangs)
              Jason_(A_Non)
              Which 11g? I know of at least five versions that fall under that marketing label. The following is always useful when reporting issues.
              select * from v$version

              Any sign of errors on the DB side? What is the session doing? Can you do an explain plan for the query that does not work? Do you have any sample data for ODG_QUERY_COLUMNS?
              • 4. Re: Issue with simple query   (it hangs)
                JackK
                SELECT * FROM v$version;
                
                Oracle Database 11g 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 Linux: Version 11.2.0.3.0 - Production
                NLSRTL Version 11.2.0.3.0 - Production
                I just discovered the reason of this issue. When the output is not pretty printed then this select hangs. If it is it works well. Very strange to me.

                ----------
                If someone know about this issue, please let me know.
                • 5. Re: Issue with simple query   (it hangs)
                  AlexAnd
                  When the output is not pretty printed then this select hangs.
                  SQL> SELECT xmlserialize(document XMLElement("QQ_QR",
                    2                             (SELECT XMLAgg(XMLElement("ROW",
                    3                                                       XMLForest(QQ_BLOCK_NAME,
                    4                                                                 QR_MODULE,
                    5                                                                 QR_NAME,
                    6                                                                 QR_DESC, --QR_SQL,
                    7                                                                 (SELECT XMLAgg(XMLElement("ROW",
                    8                                                                                           XMLForest(QC_NAME,
                    9                                                                                                     QC_DESC,
                   10                                                                                                     QC_DESC2)))
                   11                                                                    FROM ODG_QUERY_COLUMNS
                   12                                                                   WHERE qc_qr_id =
                   13                                                                         qr_id) as "QC")))
                   14                                FROM ODG_QG_QR
                   15                                JOIN ODG_QUERY_REPOSITORY
                   16                                  ON qr_id = qq_qr_id
                   17                               WHERE qq_qg_id = 2))
                   18                               indent)
                   19    FROM dual;
                   
                  XMLSERIALIZE(DOCUMENTXMLELEMEN
                  --------------------------------------------------------------------------------
                  <QQ_QR>
                    <ROW>
                      <QQ_BLOCK_NAME>DEKL</QQ_BLOCK_NAME>
                      <QR_MODULE>GOP</QR_MODULE>
                      <QR_NAME>PIS_DEKL</QR_NAME>
                      <QR_DESC>Zapytanie z danymi deklaracji</QR_DESC>
                    </ROW>
                    <ROW>
                   
                  SQL> 
                  or i misunderstood
                  can you be more specific in your question?
                  • 6. Re: Issue with simple query   (it hangs)
                    JackK
                    My query works if I uncomment
                    --.extract('/*')
                    I don't know why it hangs it the output is not pretty printed.
                    • 7. Re: Issue with simple query   (it hangs)
                      Jason_(A_Non)
                      Using the data you provided, with a tweak to remove "ODGADM" from a CREATE TABLE statement and add in QR_SQL with a value of "SELECT" for all rows in ODG_QUERY_REPOSITORY I get very similar results as you
                      Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
                      -- As provided
                      SELECT XMLElement("QQ_QR",
                                          (SELECT XMLAgg(XMLElement("ROW",XMLForest(QQ_BLOCK_NAME, QR_MODULE,QR_NAME,QR_DESC,QR_SQL,
                                                      (SELECT XMLAgg(XMLElement("ROW",XMLForest(QC_NAME,QC_DESC,QC_DESC2)))
                                                         FROM ODG_QUERY_COLUMNS
                                                        WHERE qc_qr_id = qr_id) as "QC")
                                                         )
                                                  )
                                             FROM ODG_QG_QR
                                             JOIN ODG_QUERY_REPOSITORY ON qr_id = qq_qr_id
                                            WHERE qq_qg_id = 2)
                               )
                          FROM dual
                       
                      ORA-03113: end-of-file on communication channel
                      Process ID: 27066530
                      Session ID: 134 Serial number: 159
                      
                      -- Using XMLSerialize to convert to a clob
                      SELECT XMLSerialize( DOCUMENT XMLElement("QQ_QR",
                                          (SELECT XMLAgg(XMLElement("ROW",XMLForest(QQ_BLOCK_NAME, QR_MODULE,QR_NAME,QR_DESC,QR_SQL,
                                                      (SELECT XMLAgg(XMLElement("ROW",XMLForest(QC_NAME,QC_DESC,QC_DESC2)))
                                                         FROM ODG_QUERY_COLUMNS
                                                        WHERE qc_qr_id = qr_id) as "QC")
                                                         )
                                                  )
                                             FROM ODG_QG_QR
                                             JOIN ODG_QUERY_REPOSITORY ON qr_id = qq_qr_id
                                            WHERE qq_qg_id = 2)
                               ) AS CLOB)
                          FROM dual
                       
                      ORA-03113: end-of-file on communication channel
                      Process ID: 26017918
                      Session ID: 145 Serial number: 69
                      
                      -- Proof it works as you said
                      SQL> SELECT XMLElement("QQ_QR",
                        2                      (SELECT XMLAgg(XMLElement("ROW",XMLForest(QQ_BLOCK_NAME, QR_MODULE,QR_NAME,QR_DESC,QR_SQL,
                        3                                  (SELECT XMLAgg(XMLElement("ROW",XMLForest(QC_NAME,QC_DESC,QC_DESC2)))
                        4                                     FROM ODG_QUERY_COLUMNS
                        5                                    WHERE qc_qr_id = qr_id) as "QC")
                        6                                     )
                        7                              )
                        8                         FROM ODG_QG_QR
                        9                         JOIN ODG_QUERY_REPOSITORY ON qr_id = qq_qr_id
                       10                        WHERE qq_qg_id = 2)
                       11           ).extract('/*')
                       12      FROM dual;
                      Warning: connection was lost and re-established
                       
                      XMLELEMENT("QQ_QR",(SELECTXMLA
                      --------------------------------------------------------------------------------
                      <QQ_QR><ROW><QQ_BLOCK_NAME>DEKL</QQ_BLOCK_NAME><QR_MODULE>GOP</QR_MODULE><QR_NAM
                      What those errors tell me is that I'm hitting some bug in Oracle and it is blowing out the session. Most likely with an ORA-07445 but maybe an ORA-00600. I don't have server level access to go look at what got dumped. The interesting thing is that from your setup, table ODG_QUERY_COLUMS is always empty so that inner select always returns NULL. Looks like opening a SR with Oracle would be your best bet, given the internal error that is happening in my copy of 11.2.0.3

                      Edited by: A_Non on May 7, 2013 10:54 AM
                      XMLSerialize is used to pretty print now (to expand on Odie's below comment), hence my usage above of it.
                      • 8. Re: Issue with simple query   (it hangs)
                        odie_63
                        JackK wrote:
                        My query works if I uncomment
                        --.extract('/*')
                        I don't know why it hangs it the output is not pretty printed.
                        Just FYI, extract() method doesn't pretty-print anymore in 11g.
                        • 9. Re: Issue with simple query   (it hangs)
                          JackK
                          Thanks to all :)