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.


Alain said...

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

Scott Wesley said...

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.

Dimitri Gielis said...

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.


Andre said...

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

Dimitri Gielis said...

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?


Scott Wesley said...

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.

Andre said...

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,

Dimitri Gielis said...

Hi Andre,

I did some new blog posts about JSON.

Hope that helps,

Unknown said...

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?


Dimitri Gielis said...

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,

Anonymous said...

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.

Dimitri Gielis said...

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 (linux) and it still works for me.


Anonymous said...

Hello dimitri,

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


ziZov said...

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. write('json object name', c);

And it works as expected

Sunny Patel said...

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.

Awesome said...

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

Unknown said...

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.


Dimitri Gielis said...

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,

Anonymous said...

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

jsonData = JSON.parse(response);


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 ?



John Adams said...

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)

Unknown said...

Hi Dimitri,

Thank you for the great example. I am using APEX 5.0.3 with ORDS 3.0.4 on 11gXE ( 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


Omar Aljaber said...

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

Omar Aljaber said...

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


Dan McGhan said...

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

Unknown said...

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

500 Internal Server Error

JDBCException [kind=SQL]

Antony T said...

Hi Dimitri,

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


Dimitri Gielis said...

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 so the dev team will probably answer there.


Pierre Yotti said...

Hi dimitri,

Is there a way to paginate nested json?



Dimitri Gielis said...

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?


ps3r said...

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