Monday, June 30, 2014

The APEX_JSON package: Parsing JSON in PL/SQL

As I mentioned a year ago, it looks like Apex 5.0 will include a new package called APEX_JSON for parsing and generating JSON. In this blog post, I will take a quick look at the parsing, and the next blog post will be about generating JSON output.

Since Apex 5.0 is still in Early Adopter (beta) mode and not yet released for download, we can run a query against the data dictionary (ALL_SOURCE) to view the specification of the APEX_JSON pacakge, like this:

To test the new APIs, I created a very simple test application in Apex 5.0. Here is the setup of the page in the new page designer:

And here is the running application, which allows you to enter some arbitrary JSON string and specify what part of it you want to parse out:

For those who still prefer (or need) to process the data as XML instead of JSON, there is a useful to_xmltype() function in the APEX_JSON package that can be used to turn any JSON into XML.

Simple example:

That was a quick look at the JSON parsing capabilities of Apex 5.0, yet another reason to look forward to its production release! :-)


Hendrik said...

Finally! Ive been waiting so long for a native oracle JSON package.

Ambalica said...

This looks promising!
I have to parse some incoming JSON data into a table - have you tried this before? I am relatively new to Apex so just getting lost at this point.