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:
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:
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.
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:
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;