Overview  Deprecated  Index  Generator  
PL/JSON
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD


Subtype PLJSON

This package defines PL/JSON's representation of the JSON object type, e.g.:

 {
   "foo": "bar",
   "baz": 42
 }

The primary method exported by this package is the pljson method.

Example: 
 declare
   myjson pljson := pljson('{ "foo": "foo", "bar": [0, 1, 2], "baz": { "foobar": "foobar" } }');
 begin
   myjson.get('foo').print(); // => dbms_output.put_line('foo')
   myjson.get('bar[1]').print(); // => dbms_output.put_line('0')
   myjson.get('baz.foobar').print(); // => dbms_output.put_line('foobar')
 end;

Field Summary
 NUMBERCHECK_FOR_DUPLICATE
           Private variable for internal processing.
 PLJSON_VALUE_ARRAYJSON_DATA
           Private variable for internal processing.
 PLJSON_ELEMENTSUPERTYPE
          

Method Summary
 MEMBER PROCEDURE CHECK_DUPLICATE(SELF IN OUT NOCOPY PLJSON, V_SET IN BOOLEAN)
          
 MEMBER FUNCTION NUMBER COUNT()
          

Return the number values in the object.
 MEMBER FUNCTION BOOLEAN EXIST(PAIR_NAME IN VARCHAR2)
          

Determine if a given value exists within the object.
 MEMBER FUNCTION PLJSON_VALUE GET(PAIR_NAME IN VARCHAR2)
          

Retrieve the value of a given key.
 MEMBER FUNCTION PLJSON_VALUE GET(POSITION IN PLS_INTEGER)
          

Retrieve a value based on its position in the internal storage array.
 MEMBER FUNCTION PLJSON_LIST GET_KEYS()
          

Retrieve all of the keys within the object as a pljson_list.

 myjson := pljson('{"foo": "bar"}');
 myjson.get_keys(); // ['foo']
 MEMBER FUNCTION PLJSON_LIST GET_VALUES()
          

Retrieve all of the values within the object as a pljson_list.

 myjson := pljson('{"foo": "bar"}');
 myjson.get_values(); // ['bar']
 MEMBER PROCEDURE HTP(SELF IN PLJSON, SPACES IN BOOLEAN DEFAULT FALSE, CHARS_PER_LINE IN NUMBER DEFAULT 0, JSONP IN VARCHAR2 DEFAULT NULL)
          

Print a JSON representation of the object via HTP.PRN.
 MEMBER FUNCTION NUMBER INDEX_OF(PAIR_NAME IN VARCHAR2)
          

Determine the position of a given value within the internal storage array.
 MEMBER FUNCTION PLJSON_VALUE PATH(JSON_PATH IN VARCHAR2, BASE IN NUMBER DEFAULT 1)
          

Retrieve a value from the internal storage array based on a path string and a starting index.
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN PLJSON_VALUE, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN VARCHAR2, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN NUMBER, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN BINARY_DOUBLE, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN BOOLEAN, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN PLJSON_LIST, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, ELEM IN PLJSON, BASE IN NUMBER DEFAULT 1)
          
 MEMBER PROCEDURE PATH_REMOVE(SELF IN OUT NOCOPY PLJSON, JSON_PATH IN VARCHAR2, BASE IN NUMBER DEFAULT 1)
          
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON()
          

Primary constructor that creates an empty object.

Internally, a pljson "object" is an array of values.

   decleare
     myjson pljson := pljson();
   begin
     myjson.put('foo', 'bar');
     dbms_output.put_line(myjson.get('foo')); // "bar"
   end;
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN VARCHAR2)
          

Construct a pljson instance from a given string of JSON.

   decleare
     myjson pljson := pljson('{"foo": "bar"}');
   begin
     dbms_output.put_line(myjson.get('foo')); // "bar"
   end;
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN CLOB)
          

Construct a pljson instance from a given CLOB of JSON.
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN BLOB, CHARSET IN VARCHAR2 DEFAULT 'UTF8')
          

Construct a pljson instance from a given BLOB of JSON.
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR_ARRAY IN PLJSON_VARRAY)
          

Construct a pljson instance from a given table of key,value pairs of type varchar2.
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(ELEM IN PLJSON_VALUE)
          

Create a new pljson object from a current pljson_value.
 CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(L IN OUT NOCOPY PLJSON_LIST)
          

Create a new pljson object from a current pljson_list.
 MEMBER PROCEDURE PRINT(SELF IN PLJSON, SPACES IN BOOLEAN DEFAULT TRUE, CHARS_PER_LINE IN NUMBER DEFAULT 8192, JSONP IN VARCHAR2 DEFAULT NULL)
          

Print a JSON representation of the object via DBMS_OUTPUT.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN PLJSON_VALUE, POSITION IN PLS_INTEGER DEFAULT NULL)
          

Add a pljson instance into the current instance under a given key name.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN VARCHAR2, POSITION IN PLS_INTEGER DEFAULT NULL)
          

Add a varchar2 instance into the current instance under a given key name.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN NUMBER, POSITION IN PLS_INTEGER DEFAULT NULL)
          

Add a number instance into the current instance under a given key name.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN BINARY_DOUBLE, POSITION IN PLS_INTEGER DEFAULT NULL)
          

Add a binary_double instance into the current instance under a given key name.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN BOOLEAN, POSITION IN PLS_INTEGER DEFAULT NULL)
          

Add a boolean instance into the current instance under a given key name.
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN PLJSON, POSITION IN PLS_INTEGER DEFAULT NULL)
          
 MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, PAIR_NAME IN VARCHAR2, PAIR_VALUE IN PLJSON_LIST, POSITION IN PLS_INTEGER DEFAULT NULL)
          
 MEMBER PROCEDURE REMOVE(PAIR_NAME IN VARCHAR2)
          

Remove a key and value from an object.

   declare
     myjson pljson := pljson('{"foo": "foo", "bar": "bar"}')
   begin
     myjson.remove('bar'); // => '{"foo": "foo"}'
   end;
 MEMBER PROCEDURE REMOVE_DUPLICATES(SELF IN OUT NOCOPY PLJSON)
          
 MEMBER FUNCTION VARCHAR2 TO_CHAR(SPACES IN BOOLEAN DEFAULT TRUE, CHARS_PER_LINE IN NUMBER DEFAULT 0)
          

Serialize the object to a JSON representation string.
 MEMBER PROCEDURE TO_CLOB(SELF IN PLJSON, BUF IN OUT NOCOPY CLOB, SPACES IN BOOLEAN DEFAULT FALSE, CHARS_PER_LINE IN NUMBER DEFAULT 0, ERASE_CLOB IN BOOLEAN DEFAULT TRUE)
          

Serialize the object to a JSON representation and store it in a CLOB.
 MEMBER FUNCTION PLJSON_VALUE TO_JSON_VALUE()
          

Convert the object to a pljson_value for use in other methods of the PL/JSON API.

Field Detail

CHECK_FOR_DUPLICATE

        public NUMBER CHECK_FOR_DUPLICATE
Private variable for internal processing.

JSON_DATA

        public PLJSON_VALUE_ARRAY JSON_DATA
Private variable for internal processing.
Method Detail

CHECK_DUPLICATE

public MEMBER PROCEDURE CHECK_DUPLICATE(SELF IN OUT NOCOPY PLJSON, 
                                        V_SET IN BOOLEAN)

COUNT

public MEMBER FUNCTION NUMBER COUNT()

Return the number values in the object. Essentially, the number of keys in the object.

Returns:
The number of values in the object.

EXIST

public MEMBER FUNCTION BOOLEAN EXIST(PAIR_NAME IN VARCHAR2)

Determine if a given value exists within the object.

Parameters:
PAIR_NAME - The name of the value to check for.
Returns:
true if the value exists, false otherwise.

GET

public MEMBER FUNCTION PLJSON_VALUE GET(PAIR_NAME IN VARCHAR2)

Retrieve the value of a given key.

Parameters:
PAIR_NAME - The name of the value to retrieve.
Returns:
An instance of pljson_value, or null if it could not be found.

GET

public MEMBER FUNCTION PLJSON_VALUE GET(POSITION IN PLS_INTEGER)

Retrieve a value based on its position in the internal storage array. It is recommended you use name based retrieval.

Parameters:
POSITION - Index of the value in the internal storage array.
Returns:
An instance of pljson_value, or null if it could not be found.

GET_KEYS

public MEMBER FUNCTION PLJSON_LIST GET_KEYS()

Retrieve all of the keys within the object as a pljson_list.

 myjson := pljson('{"foo": "bar"}');
 myjson.get_keys(); // ['foo']
Returns:
An instance of pljson_list.

GET_VALUES

public MEMBER FUNCTION PLJSON_LIST GET_VALUES()

Retrieve all of the values within the object as a pljson_list.

 myjson := pljson('{"foo": "bar"}');
 myjson.get_values(); // ['bar']
Returns:
An instance of pljson_list.

HTP

public MEMBER PROCEDURE HTP(SELF IN PLJSON, 
                            SPACES IN BOOLEAN DEFAULT FALSE, 
                            CHARS_PER_LINE IN NUMBER DEFAULT 0, 
                            JSONP IN VARCHAR2 DEFAULT NULL)

Print a JSON representation of the object via HTP.PRN.

Parameters:
SPACES - Enable pretty printing by formatting with spaces. Default: true.
CHARS_PER_LINE - Wrap output to a specific number of characters per line. Default: 0 (infinite).
JSONP - Name of a function for wrapping the output as JSONP. Default: null.

INDEX_OF

public MEMBER FUNCTION NUMBER INDEX_OF(PAIR_NAME IN VARCHAR2)

Determine the position of a given value within the internal storage array.

Parameters:
PAIR_NAME - The name of the value to retrieve the index for.
Returns:
An index number, or -1 if it could not be found.

PATH

public MEMBER FUNCTION PLJSON_VALUE PATH(JSON_PATH IN VARCHAR2, 
                                         BASE IN NUMBER DEFAULT 1)

Retrieve a value from the internal storage array based on a path string and a starting index.

Parameters:
JSON_PATH - A string path, e.g. 'foo.bar[1]'.
BASE - The index in the internal storage array to start from. This should only be necessary under special circumstances. Default: 1.
Returns:
An instance of pljson_value.

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN PLJSON_VALUE, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN VARCHAR2, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN NUMBER, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN BINARY_DOUBLE, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN BOOLEAN, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN PLJSON_LIST, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_PUT

public MEMBER PROCEDURE PATH_PUT(SELF IN OUT NOCOPY PLJSON, 
                                 JSON_PATH IN VARCHAR2, 
                                 ELEM IN PLJSON, 
                                 BASE IN NUMBER DEFAULT 1)

PATH_REMOVE

public MEMBER PROCEDURE PATH_REMOVE(SELF IN OUT NOCOPY PLJSON, 
                                    JSON_PATH IN VARCHAR2, 
                                    BASE IN NUMBER DEFAULT 1)

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON()

Primary constructor that creates an empty object.

Internally, a pljson "object" is an array of values.

   decleare
     myjson pljson := pljson();
   begin
     myjson.put('foo', 'bar');
     dbms_output.put_line(myjson.get('foo')); // "bar"
   end;
Returns:
A pljson instance.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN VARCHAR2)

Construct a pljson instance from a given string of JSON.

   decleare
     myjson pljson := pljson('{"foo": "bar"}');
   begin
     dbms_output.put_line(myjson.get('foo')); // "bar"
   end;
Parameters:
STR - The JSON to parse into a pljson object.
Returns:
A pljson instance.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN CLOB)

Construct a pljson instance from a given CLOB of JSON.

Parameters:
STR - The CLOB to parse into a pljson object.
Returns:
A pljson instance.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR IN BLOB, 
                                                  CHARSET IN VARCHAR2 DEFAULT 'UTF8')

Construct a pljson instance from a given BLOB of JSON.

Parameters:
STR - The BLOB to parse into a pljson object.
CHARSET - The character set of the BLOB data (defaults to UTF-8).
Returns:
A pljson instance.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(STR_ARRAY IN PLJSON_VARRAY)

Construct a pljson instance from a given table of key,value pairs of type varchar2.

Parameters:
STR_ARRAY - The pljson_varray (table of varchar2) to parse into a pljson object.
Returns:
A pljson instance.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(ELEM IN PLJSON_VALUE)

Create a new pljson object from a current pljson_value. 

   declare
    myjson pljson := pljson('{"foo": {"bar": "baz"}}');
    newjson pljson;
   begin
    newjson := pljson(myjson.get('foo').to_json_value())
   end;
Parameters:
ELEM - The pljson_value to cast to a pljson object.
Returns:
An instance of pljson.

PLJSON

public CONSTRUCTOR FUNCTION SELF AS RESULT PLJSON(L IN OUT NOCOPY PLJSON_LIST)

Create a new pljson object from a current pljson_list.

Parameters:
L - The array to create a new object from.
Returns:
An instance of pljson.

PRINT

public MEMBER PROCEDURE PRINT(SELF IN PLJSON, 
                              SPACES IN BOOLEAN DEFAULT TRUE, 
                              CHARS_PER_LINE IN NUMBER DEFAULT 8192, 
                              JSONP IN VARCHAR2 DEFAULT NULL)

Print a JSON representation of the object via DBMS_OUTPUT.

Parameters:
SPACES - Enable pretty printing by formatting with spaces. Default: true.
CHARS_PER_LINE - Wrap output to a specific number of characters per line. Default: 8192 (32512 is maximum).
JSONP - Name of a function for wrapping the output as JSONP. Default: null.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN PLJSON_VALUE, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

Add a pljson instance into the current instance under a given key name.

Parameters:
PAIR_NAME - Name of the key to add/update.
PAIR_VALUE - The value to associate with the key.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN VARCHAR2, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

Add a varchar2 instance into the current instance under a given key name.

Parameters:
PAIR_NAME - Name of the key to add/update.
PAIR_VALUE - The value to associate with the key.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN NUMBER, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

Add a number instance into the current instance under a given key name.

Parameters:
PAIR_NAME - Name of the key to add/update.
PAIR_VALUE - The value to associate with the key.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN BINARY_DOUBLE, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

Add a binary_double instance into the current instance under a given key name.

Parameters:
PAIR_NAME - Name of the key to add/update.
PAIR_VALUE - The value to associate with the key.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN BOOLEAN, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

Add a boolean instance into the current instance under a given key name.

Parameters:
PAIR_NAME - Name of the key to add/update.
PAIR_VALUE - The value to associate with the key.

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN PLJSON, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

PUT

public MEMBER PROCEDURE PUT(SELF IN OUT NOCOPY PLJSON, 
                            PAIR_NAME IN VARCHAR2, 
                            PAIR_VALUE IN PLJSON_LIST, 
                            POSITION IN PLS_INTEGER DEFAULT NULL)

REMOVE

public MEMBER PROCEDURE REMOVE(PAIR_NAME IN VARCHAR2)

Remove a key and value from an object.

   declare
     myjson pljson := pljson('{"foo": "foo", "bar": "bar"}')
   begin
     myjson.remove('bar'); // => '{"foo": "foo"}'
   end;
Parameters:
PAIR_NAME - The key name to remove.

REMOVE_DUPLICATES

public MEMBER PROCEDURE REMOVE_DUPLICATES(SELF IN OUT NOCOPY PLJSON)

TO_CHAR

public MEMBER FUNCTION VARCHAR2 TO_CHAR(SPACES IN BOOLEAN DEFAULT TRUE, 
                                        CHARS_PER_LINE IN NUMBER DEFAULT 0)

Serialize the object to a JSON representation string.

Parameters:
SPACES - Enable pretty printing by formatting with spaces. Default: true.
CHARS_PER_LINE - Wrap output to a specific number of characters per line. Default: 0 (infinite).
Returns:
A varchar2 string.

TO_CLOB

public MEMBER PROCEDURE TO_CLOB(SELF IN PLJSON, 
                                BUF IN OUT NOCOPY CLOB, 
                                SPACES IN BOOLEAN DEFAULT FALSE, 
                                CHARS_PER_LINE IN NUMBER DEFAULT 0, 
                                ERASE_CLOB IN BOOLEAN DEFAULT TRUE)

Serialize the object to a JSON representation and store it in a CLOB.

Parameters:
BUF - The CLOB in which to store the results.
SPACES - Enable pretty printing by formatting with spaces. Default: false.
CHARS_PER_LINE - Wrap output to a specific number of characters per line. Default: 0 (infinite).
ERASE_CLOB - Whether or not to wipe the storage CLOB prior to serialization. Default: true.

TO_JSON_VALUE

public MEMBER FUNCTION PLJSON_VALUE TO_JSON_VALUE()

Convert the object to a pljson_value for use in other methods of the PL/JSON API.

Overview  Deprecated  Index  Generator  
PL/JSON
SUMMARY: FIELD | TYPE | METHOD DETAIL: FIELD | TYPE | METHOD