2 Replies Latest reply: Jan 2, 2014 3:17 PM by user12110856 RSS

    ORA-19036: Invalid query result set in newContextFromHierarchy()

    user12110856

      Hi,

      I'm getting this error occasionally when using a CONNECT BY query to generate a hierarchical XML. Using Oracle 10g. The action suggested is "Make sure the query used in newContextFromHierarchy() is a CONNECT BY query or the query returns the result set have the same property as the result set generated by a CONNECT BY query." Not very helpful since I am using a CONNECT BY query which works fine 99% of the time. Appreciate any help.

       

      Thanks!

        • 1. Re: ORA-19036: Invalid query result set in newContextFromHierarchy()
          odie_63

          Hi,

           

          Can you give the exact db version please?

           

          Apart from searching among know bugs on your behalf, there's not much thing to work on in what you've given us so far.

          Could you share the query ? What about the volume of data ?

           

          Thanks.

          • 2. Re: ORA-19036: Invalid query result set in newContextFromHierarchy()
            user12110856

            Strange, never got notified that anyone replied. Oracle version is:

             

            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

            With the Partitioning, Real Application Clusters, OLAP, Data Mining

            and Real Application Testing options

             

            Volume of data is not large.

             

            DBMS_XMLGEN.NEWCONTEXTFROMHIERARCHY('SELECT plevel,

                            XMLELEMENT("PORTFOLIO",

                                       XMLELEMENT("MPFL_ID", MPFL_ID),

                                       XMLELEMENT("MPFL_NAME", MPFL_NAME),

                                       XMLELEMENT("MPFL_DESC", MPFL_DESC),

                                       XMLELEMENT("PFL_ID", PFL_ID),

                                       XMLELEMENT("PFL_NAME", PFL_NAME),

                                       XMLELEMENT("PFL_DESC", PFL_DESC),

                                       XMLELEMENT ("PFTYP_NAME", PFTYP_NAME),

                                       XMLELEMENT("HIDE", HIDE),

                                       XMLELEMENT("REF_MGR", REF_MGR))

                        FROM (WITH TEST_TREE AS..

            ..

            ..

             

            ..

             

            Can't share the exact query but it's using a WITH clause which contains a query using a CONNECT BY NOCYCLE. There are several UNION ALLs of the result set generated by the WITH clause.

             

            Thanks for your help!

             

            Message was edited by: user12110856