PL/JSON

The goal of PL/JSON is to create a correct implementation of JSON to use in a PL/SQL environment. The Oracle object syntax has been chosen to ensure a straightforward and easy way to decode and encode JSON. PL/JSON is delivered AS IS and we cannot make any guarantee or be held responsible to any unwanted effects that may arise from using this software. However, we would like to stress that we have tested, and used this software, and believe that it is a safe product to use.

Features of PL/JSON include:

• Basic JSON Path support
• Optional "tweaks" to adjust PL/JSON's behavior

The PL/JSON distribution consists of:

• An install script
• An uninstall script
• Four new Oracle types ready to use in your database
• Three packages (parser, printer and extension)
• A few example files
• Some testing scripts (creates and delete a table named pljson_testsuite)
• Optional add-ons packages:
  • pljson_dyn A package that enables you to generate JSON from sql. Nested queries are not supported. See example 16 for more information.
  • pljson_ml A package that converts from XML to JSON using a XSLT stylesheet. See www.jsonml.org.
  • pljson_xml A package that converts a JSON object to XML.
  • pljson_helper Work on JSON with set operations.
  • pljson_util_pkg Written by Morten Braten (http://ora-00001.blogspot.com). Generate JSON from sql using a XSLT stylesheet.
  • pljson_ac Autocomplete package. Some PL/SQL IDEs provide autocompletion when using a package but not when using an object type. This package is a wrapper around the methods on pljson, pljson_list and pljson_value to assist such IDEs.

Known limitations of PL/JSON are:

• Key names are limited to 4000 characters
• The number parsing assumes that Oracle's number type can contain the input (in most cases it can)

If you need help with PL/JSON, you can post questions on StackOverflow using the pljson tag. Or create a new issue on our GitHub issues tracker. There are also quite a few good articles written about PL/JSON that are a simple Google search away.

The remainder of this document provides an overview of using PL/JSON along with some examples. The PLDoc generated documentation is also available.

Overview

Basically PL/JSON can be used in two ways: Either you manually build up an object structure and emit JSON text with the to_char method or you parse JSON text into a object structure and use the objects in PL/SQL. Obviously you could also parse JSON text into objects, modify these and then emit JSON text. There are only three objects you should know: pljson, pljson_list and pljson_value. The pljson object can hold an object described by the { } syntax and is named pljson rather than pljson_object to keep the name short. The pljson_list object can hold an array described with the [ ] syntax. The postfix "list" was chosen over "array" for two reasons, one: to keep it short, two: there seems to be a naming standard in Oracle types that the postfix "array" is being used to describe types with the "table of" construction. The last type pljson_value contains the primitive simple types (strings, numbers, bools, null), but can also contain an array or an object. The object model for PL/JSON is shown in figure PL/JSON Object Model:

Behavior and Error Handling

Input to the parser is expected to be in the charset of the database. The objects that are generated contain unescaped values that will be escaped when emitted through the printer. To ensure correct JSON output, even from non-UTF databases, only ASCII chars are emitted. All the characters which are not part of ASCII will be escaped.

The errors or exceptions that PL/JSON may throw, can be caught with the following code:

declare
  scanner_exception exception;
  pragma exception_init(scanner_exception, -20100);
  parser_exception exception;
  pragma exception_init(parser_exception, -20101);
  jext_exception exception;
  pragma exception_init(jext_exception, -20110);
  ...
begin
  -- … json code …
exception
  when scanner_exception exception then …
  when parser_exception exception then …
  when jext_exception exception then …
end;