Relational to JSON with APEX_JSON

By July 8, 2015 Uncategorized

APEX_JSON is a new PL/SQL API included with Oracle Application Express (APEX) 5.0 that provides utilities for parsing and generating JSON. While APEX_JSON was primarily intended to be used by APEX developers, there are some hooks that can allow it to be used as a standalone PL/SQL package.

Solution

The following solution uses APEX_JSON to create the JSON that represents a department in the HR schema. APEX_JSON basically writes JSON content to a buffer. By default, the buffer used is the HTP buffer in the database as that’s what APEX reads. But as you can see with line 27, it’s possible to redirect the output to a CLOB buffer instead. Once we’ve redirected the output, we can make API calls to open/close objects & arrays and write values to them. When we’re done writing out the JSON we can make a call to get_clob_output to get the JSON contents. I’ve highlighted some of the relevant lines…

Output

When passed a department id of 10, the function returns a CLOB populated with JSON that matches the goal 100%.

Summary

I really enjoyed working with APEX_JSON – it’s my new “go-to” for PL/SQL based JSON generation. APEX_JSON has a very light footprint (it’s just a single package) and it takes a minimalistic approach. Rather than compose objects as one would do with PL/JSON, you simply use the package to write JSON to a buffer.

This approach yields some performance benefits as well. In a basic test where I generated the JSON for every department in the HR schema 100 times in a loop, the APEX_JSON based solution finished at around 3.5 seconds whereas the PL/JSON based solution took around 17 seconds. That means APEX_JSON about 3.8 times faster than PL/JSON when it comes generating JSON and converting it to a CLOB.

Unfortunately, APEX_JSON is only included with APEX 5.0+. Upgrading your database’s APEX instance seems a little extreme if all you want to do is work with JSON (though it is free and doesn’t take too long), but if you already have APEX 5.0 then it’s a very nice tool to be able to leverage.

4 Comments

  • Scott Wesley says:

    What’s the advantage of writing to a CLOB instead of direct to the HTP buffer?

    • danmcghan says:

      Hi Scott,

      Thanks for your question! There’s no advantage! 🙂 In fact, if what you’re doing is in the context of a web application then it’s more performant to write directly to the HTP buffer, avoiding the additional buffering to a CLOB.

      The goal in this example was simply to convert the relational data to JSON. For that you don’t “need” to be in the context of a web application – but to do it outside of that context you do have to redirect the default output. Also, I didn’t want my testing to any network latency when comparing APEX_JSON to PL/JSON.

      Regards,
      Dan

  • Scott Wesley says:

    Thanks, that makes sense.
    I found the style stood out between my example and Erick’s first reply here https://community.oracle.com/message/13618534
    I executed my anon block in APEX SQL Workshop so I saw htp buffer, Erick sent his to dbms_ouput, and you return the clob, which could be used however the client process wants, right?

Leave a Reply