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:

The PL/JSON distribution consists of:

Known limitations of PL/JSON are:

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:

object model graph

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;