Friday, September 27, 2013

RESTful Cursor support for JSON

Just a real quick blog before I forget.  In the latest APEX Listener 2.0.4 patch, there's support for nested cursors.  There is two gotchas.  First make sure to to disable ( make 0 ) the pagination of the REST definition.  The second is this only works at the top level, so not nested nests of nests.

This is a very quick example of tables and nested in each table the columns and indexes that tables has.


select t.table_name,
  cursor(select column_name
             from user_tab_cols tc 
             where tc.table_name = t.table_name) cols ,

  cursor(select index_name
             from user_indexes i 
             where i.table_name = t.table_name) indexes
 from user_tables t


The resulting JSON looks like this.