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.

Leave a Reply