6 Replies Latest reply on Oct 22, 2013 9:46 PM by TobiasE

    Listener 2.0.3 nested JSON

    TobiasE

      Hello,

       

      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,
                 t1.b,
                 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?

       

      Versions:

       

      APEX Listener 2.0.3

      WebLogic Server 10.3.6

      DB 11.2.0.3

       

      Thanks,

      Tobias