Sunday, January 18, 2015

Generate nested JSON from SQL with ORDS and APEX 5

When creating web applications, at some point you will need to interact with JSON. Either you consume JSON or you need to generate it to be able to use that cool widget you found.

A few years ago when I wrote about interacting and customising charts, XML was the data format. Today JSON is more common to use as it works so well with JavaScript. I needed to pass some nested JSON - here's an example: multiple customers have multiple orders and an order consists out of multiple items: 

What is the easiest way to generate that JSON data?

Luckily we have Oracle REST Data Services (ORDS), it literally is just one SQL statement!
Here you go: 


Save and presto you're done :) Hit the TEST button and copy/paste the generated JSON in for example JSONLint to validate the output and yep we have valid JSON.


But that is not all - it gets even better. APEX 5 comes with a new package apex_json which contains so many goodies, really a wonderful piece of code. The same SQL statement I call with the package and again I get the JSON I need. It's slightly different from what ORDS generated - ORDS has an items tag surrounding the data, but again it's valid JSON.
Note that APEX 4.2 has some JSON support too (apex_util.json_from_sql), but it doesn't go as far as the package you find in APEX 5.0 - for example the cursor syntax is not working there, but for straight forward JSON it does it job.


So this shows how easy it is to generate JSON these days. It has been different where we had to use the PL/JSON package (which was good too, but build-in is what I prefer).

And finally with Oracle Database 12c, and the JSON support straight in the database, consuming and querying JSON has been made so much easier, but that is for another post. If you can't wait, the link will bring you to the official 12c Database JSON doc.
at

30 comments:

  1. Alain1/18/2015 7:01 PM

    Very nice !Indeed a must-know when you intend to create REST services with Apex ! Thank you !
    Alain

    ReplyDelete
  2. Scott Wesley1/19/2015 8:36 AM

    Sorry Dimitri, I think this post is missing detail that many other posts also don't detail. Namely how to create then consume the web service.

    I'm interested on what you'll mention with the 12c content because generating content again seems to be missing.

    ReplyDelete
  3. Dimitri Gielis1/19/2015 8:39 AM

    Hi Scott,

    This post is about generating JSON, I'll do another post on consuming JSON, but you have many possibilities to consume (from within the database, from the client with javascript/jquery).
    So it depends a bit what you want to achieve.

    Dimitri

    ReplyDelete
  4. Andre1/19/2015 3:43 PM

    Hi Dimitri, thank you for taking your time to share your knowledge about apex with us, it been great so far and cant wait to see what comes next.

    I also would like to know more about how to create and consume web service using oracle REST feature.

    Thank you
    André!

    ReplyDelete
  5. Dimitri Gielis1/19/2015 7:27 PM

    Hi Andre,

    Would you like to see how first you use ORDS to provide REST webservices and then consume from within the database or APEX?

    Thanks,
    Dimitri

    ReplyDelete
  6. Scott Wesley1/20/2015 2:28 AM

    I'm with Andre. You mention how easy it is to generate JSON with ORDS supplying a SQL statement, but there are plenty of mechanics around it that have been skipped over in this post.

    ReplyDelete
  7. Andre1/21/2015 2:25 PM

    Hi Dimitri,

    I would like to see a little example from real world, so I can consume data from other rest service that is already created and one I can create a REST to do the usual like select, insert and update my data. All using Oracle Apex and XE 11g.

    The way you explain how things works I find it very easy to understand and a very simple and functional example would do the trick.

    REST and JSON are kind of knew for me and I believe the same for a lot of other APEX developers, cant wait for your new post about consuming json and its many possibilities.

    Thank you again for your time,
    André

    ReplyDelete
  8. Dimitri Gielis1/23/2015 9:26 AM

    Hi Andre,

    I did some new blog posts about JSON.
    - http://dgielis.blogspot.be/2015/01/json-for-apex-developers-part-1.html
    - http://dgielis.blogspot.be/2015/01/json-for-apex-developers-part-2.html

    Hope that helps,
    Dimitri

    ReplyDelete
  9. Unknown4/24/2015 5:57 PM

    Thanks Dimitri, so, pljson and other methods used string concatenation, one of the slowest operations in plsql.

    With ORDS, we observed that the pure SQL web services were significantly faster, leveraging java to perform the json conversion.

    Have you observed any significant performance improvements with plsql web services using the native json capability?

    Thanks!

    ReplyDelete
  10. Dimitri Gielis4/24/2015 6:59 PM

    Hi Jerry,

    I haven't done real performance/stress testing on json services yet. I propose you try both in your environment and see which ones work best for your environment.

    Hope that helps,
    Dimitri

    ReplyDelete
  11. Anonymous6/02/2015 8:32 PM

    Hello dimitri,

    I tried to use the apex_json package in oracle 12c but i have an ORA-22902 error when i try to introduce a cursor in my query. do you know if it's still supported?
    thanks in advance.
    Simo

    ReplyDelete
  12. Dimitri Gielis6/02/2015 9:40 PM

    Hi Simo,

    As far as I know it's supported yes, but some people get that error, but I've no idea way.
    I'm on 12.1.0.2.0 (linux) and it still works for me.

    Dimitri

    ReplyDelete
  13. Anonymous6/10/2015 7:44 PM

    Hello dimitri,

    Thank you for response, we will try another database (oracle 11.2.0.4 database standard edition one ), and shar the result with you,

    Simo,

    ReplyDelete
  14. ziZov7/18/2015 1:32 AM

    Very informative, as Always, thank you Dimitri.
    Just wanted to say that for some reason, the simple output procedure apex_json.write(sys_refcursor) works as expected ONLY if you don't have nested Cursors.

    I couldn't get a comparable query with nested cursors to the example shown above in the screenshot to work. No errors, just no output.
    with some digging around I ended up following what the documentation "seems to recommend" as:

    apex_json.open_object;
    apex_json. write('json object name', c);
    apex_json.close_object;

    And it works as expected

    ReplyDelete
  15. Sunny Patel10/16/2015 1:14 PM

    As per ORA-22902 error mentioned above, I too was getting this error and overcome this by setting Pagination Size to 0 (zero). This is set in RESTful Service Module page.

    ReplyDelete
  16. Awesome10/30/2015 5:22 PM

    This would help reader of this blog : JSON Formatter and JSON Viewer for JSON Lover

    ReplyDelete
  17. Unknown2/09/2016 12:22 AM

    Hi Dimitri, is this still working with ords3? I get a ORA-22902: CURSOR expression not allowed when trying to use the following statement:

    select dept.*,
    cursor(select emp.* from emp where emp.deptno=dept.deptno)
    from dept

    Everything is soo buggy, drives me crazy.

    Thanks,
    ~Dietmar.

    ReplyDelete
  18. Dimitri Gielis2/14/2016 12:58 AM

    Hi Dietmar,

    My guess is that your pagination size is set to a number, which I saw causes this issue.
    I always put my pagination size to 0. (both at module and get level)

    Hope that helps,
    Dimitri

    ReplyDelete
  19. Anonymous3/10/2016 12:50 AM

    is there a way to get rid of the items parent element from the json generated by ORDS ? I use


    jsonData = JSON.parse(response);

    jsonData.items


    But in some places I need to set the source as URL directly. So without writing a custom application process using apex_json.write can I do it in ORDS restful query ?



    thanks

    -A

    ReplyDelete
  20. John Adams4/20/2016 5:33 PM

    Dimitri, that pagination tip was awesome! The cursor error message is very cryptic, how did you figure that out?

    Btw- we were working in the same group under Marcie recently while you were contracting here. (Mark Lindros)

    ReplyDelete
  21. Unknown4/21/2016 6:24 PM

    Hi Dimitri,

    Thank you for the great example. I am using APEX 5.0.3 with ORDS 3.0.4 on 11gXE (11.2.0.2) on a Win 10 laptop. I'm fairly new to APEX but have been using Oracle for 30 years.

    I tried using just a single level cursor in a query to create a nested json response, but
    received the error:
    500 Internal Server Error

    Error during evaluation of resource template: GET po, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed.

    This appears to point to a restriction about using cursors below the top level.

    I tried the same thing in SQL Plus and it works perfectly. In SQL Workshop inside APEX
    it also fails...

    This was my sql statement:
    select o.po_no, o.po_dt, o.vend_id, o.shipvia_cd, o.drop_ship, o.notes,
    cursor ( select i.coin_id, i.line_desc, i.cert_no, i.unit_price, i.price_um_cf, i.um_id, i.nonstk, i.ord_qty
    from po_line i
    where o.po_no = i.po_no ) items
    from po o
    where o.po_no = 6

    Any ideas what I might be doing wrong? Or configured incorrectly?

    Thank you

    Bryan

    ReplyDelete
  22. Omar Aljaber4/27/2016 11:47 AM

    Thank you for you post, but I am facing this issue ORA-00932: inconsistent datatypes: expected NUMBER got CURSER, and this Error during evaluation of resource template: GET getsub, SQL Error Code: 22,902, SQL Error Message: ORA-22902: CURSOR expression not allowed

    ReplyDelete
  23. Omar Aljaber4/27/2016 11:50 AM

    I am facing the same issue, in toad working good, but in apex not working.

    Thanks

    ReplyDelete
  24. Dan McGhan5/27/2016 5:54 PM

    To the folks getting "ORA-22902: CURSOR expression not allowed" with nested cursor expressions, please check the following:
    1. The pagination size must be set to 0 for the module AND the handler
    2. Cursor expressions can only be in the top level
    3. Always use the latest versions of ORDS and APEX

    ReplyDelete
  25. Unknown6/08/2016 3:28 PM

    Thanks for the suggestion with the pagionation set to 0. Dont get ORA-22902 anymore but

    500 Internal Server Error

    JDBCException [kind=SQL]

    ReplyDelete
  26. Antony T10/15/2016 2:31 PM

    Hi Dimitri,

    Is there a way to change the json output tag "{"items":" to something customized?

    Thanks
    Antony

    ReplyDelete
  27. Dimitri Gielis10/15/2016 6:09 PM

    Hi Antony,

    I'm not aware you can do that, or it must be a new feature in a newer release of ORDS.
    I see you already submitted on OTN https://community.oracle.com/thread/3981666 so the dev team will probably answer there.

    Thanks,
    Dimitri

    ReplyDelete
  28. Pierre Yotti4/05/2018 9:46 PM

    Hi dimitri,

    Is there a way to paginate nested json?

    Regards

    Pierre

    ReplyDelete
  29. Dimitri Gielis4/06/2018 10:13 AM

    Hi Pierre,

    You mean do pagination for just a subset?
    I never tried that, but my first thought that is not possible.
    The pagination size you specify is for the entire result set not just for a specific cursor as far as I know, but you might want to check on the ORDS forum.

    As a workaround you could write your own where clause and define some parameters in the sql of the cursor and paginate like that.
    Is that an option?

    Dimitri

    ReplyDelete
  30. ps3r7/11/2021 10:40 AM

    Nice work but I am searching for nested json from tables query..
    Can u suggest me that ?


    Thanks

    ReplyDelete