Relational to JSON with PL/SQL

By November 2, 2018 Uncategorized

In the last post in this series, I demonstrated how powerful functions added to the SQL engine in Oracle Database 12.2 allow you to generate JSON with ease. But what if you were doing something sufficiently complex that it required the procedural capabilities that PL/SQL provides? Well, you’re covered there too! In this post, I’ll show you how new JSON based object types can be used to get the job done.


The 12.2+ PL/SQL object types available for working with JSON are:

  • JSON_ELEMENT_T – supertype of the other JSON types (rarely used directly)
  • JSON_OBJECT_T – used to represent JSON objects
  • JSON_ARRAY_T – used to represent JSON arrays
  • JSON_SCALAR_T – used to represent scalar values in JSON (strings, numbers, booleans, and null)
  • JSON_KEY_LIST – lesser used collection type for object keys

In the following solution, I use the JSON_OBJECT_T and JSON_ARRAY_T types to generate the desired JSON output. Smaller data structures are used to compose larger ones until I have the JSON object I want. Then I use the to_clob method to serialize the in-memory representation to JSON.


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


The JSON types for PL/SQL are a very welcome addition to Oracle Database. I’ve only demonstrated how to build up objects in memory to generate JSON, but there are many other methods for modification, serialization, introspection, and so on.

If you’ve seen the PL/JSON solution, you’ll note that the code is very similar since they both use the object-oriented capablities of Oracle Database (as opposed to APEX_JSON which is more procedural). When compared to PL/JSON, the main advantages to the 12.2+ built-in types are:

  • Simplicity: There’s no installation needed.
  • Documentation: The JSON Developer’s Guide provides some getting started content in Part IV: PL/SQL Object Types for JSON and the PL/SQL Packages and Types Reference provides additional API details.
  • Peformance: I ran a small test on a local 18c XE database where I generated the JSON for each department in the HR schema 100 times. The PL/JSON solution took about 4.6 seconds on average while the solution in this post and the APEX_JSON solution both took around 1.5 seconds.

Having said all that, if you’re not yet using Oracle Database 12.2+, then PL/JSON is still a great option for working with JSON. The PL/JSON team continues to build out the APIs, address issues, and develop the documentation.


  • LewisC says:

    I remember when I wrote the first version of PL/JSON. Not many DB people, at least not Oracle DB people, had any familiarity with JSON. I wrote it to handle some requirements I had on a project. Once I had a version that worked for me I released it on SourceForge.

    I was expecting Oracle would add JSON support eventually. I kind of expected it to be a lot like the XMLType so I tried to make mine like that. I kept it as simple as possible.

    I opened it to the people who eventually took it over. I think the person I gave admin to is still the one leading the development. He was the last time I checked anyway. That was probably a few years ago.

    I didn’t really want to maintain it but I had a lot of fun writing it. I had the version that worked for me. I do think it got more complex over time but probably added more features too.

    Fun stuff and good times.


    • danmcghan says:

      I remember when it was on SourceForge – good times indeed… Wait, I think we’re dating ourselves!