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.
30 comments:
Very nice !Indeed a must-know when you intend to create REST services with Apex ! Thank you !
Alain
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.
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
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é!
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
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.
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é
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
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!
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
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
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
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,
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
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.
This would help reader of this blog : JSON Formatter and JSON Viewer for JSON Lover
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.
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
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
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)
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
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
I am facing the same issue, in toad working good, but in apex not working.
Thanks
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
Thanks for the suggestion with the pagionation set to 0. Dont get ORA-22902 anymore but
500 Internal Server Error
JDBCException [kind=SQL]
Hi Dimitri,
Is there a way to change the json output tag "{"items":" to something customized?
Thanks
Antony
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
Hi dimitri,
Is there a way to paginate nested json?
Regards
Pierre
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
Nice work but I am searching for nested json from tables query..
Can u suggest me that ?
Thanks
Post a Comment