Relational to JSON with PL/JSON

By July 8, 2015 Uncategorized

PL/JSON is an open source library for working with JSON in Oracle Database. Originally started in 2009, the project has undergone many updates and bug fixes over the years and is now quite stable and feature rich. PL/JSON was first hosted on SourceForge (and it’s still available there) but it’s probably best to get it from its new home on GitHub.

After downloading and extracting the contents of the project, installation is as simple as running the install.sql script from a client like SQL Plus or SQL Developer. The installation scripts install a total of 9 packages and 4 object types. The core of PL/JSON is based on 2 object types, JSON and JSON_LIST, which correspond to the two types of structures that exist in JSON: objects and arrays.


The following solution uses PL/JSON to create the JSON that represents a department in the HR schema. Because PL/JSON makes use of some of the object-oriented features of Oracle Database, the solution is somewhat object-oriented as well. Smaller data structures are created and used to compose a larger JSON object which represents our department. Finally, a call to the to_clob method of the JSON object serializes the JSON so that we can move it around. I’ve highlighted some of the relevant lines…


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


All in all, I really like PL/JSON. Having been in the open source community for so long now it’s grown into a fairly robust and stable library. Also, because it’s open source, it can be downloaded and installed in any database. I tested it in an 11g XE instance without any issues and I believe it would work fine in 10g too.

That’s not to say PL/JSON isn’t without its faults. PL/JSON can take some time to learn and get used to. For example, on line 141 of the solution above, you’ll see an employee JSON object being appended to the employees JSON_LIST. However, the JSON object can’t be appended directly, you must append the result of a call to to_json_value() instead. But the same is not true for the put method, which will take JSON or JSON_VALUE types. I’m sure there’s a good reason for this, but it’s still confusing.

Another problem with PL/JSON is the documentation. The homepage on GitHub says, “To learn the API, look at the files in /examples”. The examples are fine, but proper API documentation would be very welcome and benefit the entire community. Anyone feel like submitting some pull requests? 🙂

Finally, I’d heard complaints about performance issues with PL/JSON so I decided to run a test. On a local 18c XE database, 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.

Post Oracle Database 12.1+ issues

If you use PL/JSON with Oracle Database 12.1+, then you may run into some small issues. I’ll list any issues I identify here.

  • JSON_VALUE – PL/JSON installs a synonym named JSON_VALUE, which points to a type named PLJSON_VALUE. Unfortunately, JSON_VALUE happens to be the name of function added to Oracle Database 12.1 for quering JSON values from documents. You may get the following error if the compiler picks up on the built-in when you intend to use PL/JSON’s version: Error(98,48): PLS-00103: Encountered the symbol “.” when expecting one of the following: ( . The work around is simple enough, just prefix JSON_VALUE with the schema name where PL/JSON was installed (as was done on line 98 in the solution above) or reference the PLJSON_VALUE type directly.


  • James Sumners says:

    Thank you for the great article. It is true, documentation is a big sore spot and it’s one I’m working on at least improving for an upcoming 2.0.0 release.

    And as you said, we are always open to a pull request 🙂

    (I’m a PL/JSON maintainer.)

    • Vikr says:

      In the above example the author assumed all the values exists. What if a value is blank. How do i use the put function to pass null value ?

      “jobHistory”: [
      “id”: “AD_ASST”,
      “departmentId”: null,
      “startDate”: “17-SEP-1995”,
      “endDate”: “17-JUN-2001”

  • Mark Anderson says:

    Thank you for a good example. I’m sharing the URL with our team.

Leave a Reply