6 Replies Latest reply: Oct 22, 2013 4:46 PM by TobiasE RSS

    Listener 2.0.3 nested JSON




      I noticed the following on the Bugs Fixed page for the most recent APEX Listener version:


      • Added support for rendering nested results to JSON. Queries can now include nested top level cursors, which are automatically translated into nested JSON arrays. Note this feature does not work with RESTful Services that are using automatic pagination.


      This sounded interesting, so I tried it out. I assumed it meant I could write the handler query as something like this:


      select t1.a,
                 cursor(select t2.a,t2.b,t2.c
                              from table2 t2
                              where t2.t1_id = t1.id) t2_rows
      from table1 t1


      However, this doesn't work (at least in my case), I get internal server error when calling the service, along with the following lines in the server log:


      oracle.dbtools.rt.web.WebErrorResponse internalError

      SEVERE: null

      JDBCException [kind=SQL]

      at oracle.dbtools.common.jdbc.JDBCException.wrap(JDBCException.java:99)

      at oracle.dbtools.common.jdbc.ResultSetIterator.advance(ResultSetIterator.java:80)

      at oracle.dbtools.common.jdbc.ResultSetIterator.next(ResultSetIterator.java:54)

      at oracle.dbtools.common.jdbc.ResultSetIterator.next(ResultSetIterator.java:18)

      at oracle.dbtools.common.util.Iterators$TransformIterator.next(Iterators.java:701)

      at oracle.dbtools.common.util.Iterators$UncloseableIterator.next(Iterators.java:744)



      I should add that the query I used works fine in SQL Developer.


      Have I misunderstood this new feature, and if so, could someone shed some light on how it's supposed to work?




      APEX Listener 2.0.3

      WebLogic Server 10.3.6





        • 1. Re: Listener 2.0.3 nested JSON
          Colm Divilly-Oracle

          Thanks Tobias,

          that looks like a bug, your query should work (as long as the pagination size is set to zero), I'll look into it. This issue is tracked with BUG: 17416800



          • 2. Re: Listener 2.0.3 nested JSON

            Hi Colm,


            I noticed the following on Kris' blog:




            Is this supposed to be fixed in 2.0.4? I seem to be getting the same errors as before after upgrading.




            • 3. Re: Listener 2.0.3 nested JSON
              Colm Divilly-Oracle

              No Tobias, your bug was not yet addressed in 2.0.4. As Kris's post shows we do support nested cursors, but seems like your query is an unsupported case, it will be addressed in a subsequent release.

              • 4. Re: Listener 2.0.3 nested JSON

                Hopping on this thread to follow it, as I'm having the same 500 errors as Tobias with v2.0.4 of the Listener.


                However, my data is such that not all of my test cases return data for the nested cursor query.  I'm using a bind variable and when the cursor comes back with an empty set, I don't get a 500 error from the Resource Handler test--everything comes back as expected and there's an empty JSON array where the nested cursor would be.


                But in the test cases where there's data returned for the nested cursor, I throw a 500 error like Tobias.


                Meanwhile I also could get my nested cursors to work just fine in SQLDeveloper, but I noticed something odd in the data grid results in SQLDev for my cursor column INDUSTRIES when it wasn't empty:  the data returned looked a little like an array wrapped with curly brackets, but the last entry had a trailing comma:

                {<INDUSTRY=Education & Research>,<INDUSTRY=Public Sector>,<INDUSTRY=Industrial Manufacturing>,<INDUSTRY=High Technology>,}


                Of course, no trailing comma in SQLDev for the INDUSTRIES column if the cursor didn't return any data:



                And that second scenario works just fine via the RESTful Resource Handler test harness (no 500 error from the APEX Listener):


                • items:


                  • {
                    • id: "301012195007062390326290355934712173722",
                    • updated: "2013-09-27T18:48:42.48Z",
                    • row_key: "PTHADEN",
                    • other_keys: "other values",
                    • industries: [ ]


                • ]



                Is the trailing comma returned by the cursor significant?  Maybe parsing it the thing that's throwing off the APEX listener?  I couldn't figure a way to strip it off to test, as I would get ORA-22902 errors if the cursor statement wasn't at the very top level of my outer select statement.

                • 5. Re: Listener 2.0.3 nested JSON

                  FYI to close the loop:  Listener v2.0.5 was the solution to get my nested cursor working.  http://www.oracle.com/technetwork/developer-tools/apex-listener/downloads/index.html



                  • 6. Re: Listener 2.0.3 nested JSON

                    Yes indeed, it's working for me too with 2.0.5.