2 Replies Latest reply on May 10, 2016 10:57 AM by Simon

    Cursor Expressions with Feed Source Type

    Simon

      Hi everyone.

       

      We have discovered a problem when using a query that contains a cursor expression in our source_type_collection_feed GET handler. We’re using cursor expressions to build hierarchy into our JSON dataset because ORDS converts nested cursors into JSON arrays (thanks for that).

       

      With this type of query and the p_source_type of source_type_collection_feed (for multiple rows) we get a 500 response with the detailed ORDS error:

      declares the following explicit parameters, but does not reference them: page_size


      We have no idea what the parameter page_size is. If we just change the p_source_type to source_type_collection_item then all is well, a 200 response and the nested cursor converted into a JSON array. Of course, this only returns one row from our dataset so it’s not suitable for us but it does prove that nested cursors are valid in ORDS.


      My question, is this a known issue or by design?

       

      Many thanks for ORDS.

        • 1. Re: Cursor Expressions with Feed Source Type
          rhardey

          Hi Simon,

           

          Unfortunately this is a limitation of how pagination is handled by ORDS.

           

          E.g if you have the following query in your handler:

           

          select 'Attribute 1' attr1

          ,cursor(select 'Line 1 Attribute 1' attr1 from dual) lines

          from dual

           

          then ORDS wraps this as follows in order to provide pagination:

           

          select * from (

          select q_.* , row_number() over (order by 1) rn___ from (

          select 'Attribute 1' attr1

          ,cursor(select 'Line 1 Attribute 1' attr1 from dual) lines

          from dual) q_

          )

          where rn___ between :row_offset and :row_count

           

          This is a clever way to handle pagination in general but doesn't work with cursor expressions as this then leads to the error:

           

          ERROR at line 4:

          ORA-22902: CURSOR expression not allowed

           

          I.e. the Oracle DB requires that CURSOR expressions must only appear in top level queries, which is not the case for the above SQL statement.

           

          Simply put, you can't use CURSOR expressions in GET handlers with collection source types.  I've run in to the same issue, but have found that if I only include CURSOR expressions in GET handlers of item source types, and leave them out of the associated collection types it doesn't prove to be too much of a limitation.

           

          To see the error for yourself you can turn on debugging output in defaults.xml:

           

          <entry key="debug.printDebugToScreen">true</entry>

           

          Regards,

          Ryan.

          1 person found this helpful
          • 2. Re: Cursor Expressions with Feed Source Type
            Simon

            Thanks for this Ryan. I suspected it would be about pagination. So, our solution in this instance is to use an item type and then surround the query in another cursor expression so that the single item returned is actually one big JSON array. We lose pagination completely but at least we get the nesting we need. Some may find that helpful.

            1 person found this helpful