This package includes utilities that parse and generate JSON.
To read from a string that contains JSON data, first use parse() to convert the string to an internal format. Then use the get_% routines (e.g. get_varchar2(), get_number(), ...) to access the data and find_paths_like() to search.
Alternatively, use to_xmltype() to convert a JSON string to an xmltype.
This package also contains procedures to generate JSON-formatted output. Use the overloaded open_%(), close_%() and write() procedures for writing.
This example parses a JSON string and prints the value of member variable "a".
DECLARE
s varchar2(32767) := '{ "a": 1, "b": ["hello", "world"]}';
BEGIN
apex_json.parse(s);
sys.dbms_output.put_line('a is '||apex_json.get_varchar2(p_path => 'a'));
END;
This example converts a JSON string to XML and uses XMLTABLE to query member values.
select col1, col2
from xmltable (
'/json/row'
passing apex_json.to_xmltype('[{"col1": 1, "col2": "hello"},'||
'{"col1": 2, "col2": "world"}]')
columns
col1 number path '/row/col1',
col2 varchar2(5) path '/row/col2' );
This example writes a nested JSON object to the HTP buffer.
BEGIN
apex_json.open_object; -- {
apex_json.write('a', 1); -- "a":1
apex_json.open_array('b'); -- ,"b":[
apex_json.open_object; -- {
apex_json.write('c',2); -- "c":2
apex_json.close_object; -- }
apex_json.write('hello'); -- ,"hello"
apex_json.write('world'); -- ,"world"
apex_json.close_all; -- ]
-- }
END;
The following are constants used for the parser interface:
subtype t_kind is binary_integer range 1 .. 7; c_null constant t_kind := 1; c_true constant t_kind := 2; c_false constant t_kind := 3; c_number constant t_kind := 4; c_varchar2 constant t_kind := 5; c_object constant t_kind := 6; c_array constant t_kind := 7;
JSON data is stored in an index by varchar2 table. The JSON values are stored as records. The discriminator "kind" determines whether the value is null, true, false, a number, a varchar2, an object or an array. It depends on "kind" which record fields are used and how. If not explicitly mentioned below, the other record fields' values are undefined:
* c_null: -
* c_true: -
* c_false: -
* c_number: number_value contains the number value
* c_varchar2: varchar2_value contains the varchar2 value
* c_object: object_members contains the names of the object's members
* c_array: number_value contains the array length
type t_value is record ( kind t_kind, number_value number, varchar2_value varchar2(32767), object_members wwv_flow_t_varchar2 ); type t_values is table of t_value index by varchar2(32767);
c_date_iso8601 constant varchar2(30) := 'yyyy-mm-dd"T"hh24:mi:ss"Z"';
g_values t_values;
e_parse_error exception; pragma exception_init(e_parse_error, -20987);
This procedure closes all objects and arrays up to the outermost nesting level.
APEX_JSON.CLOSE_ALL;
None.
This procedure writes a close bracket symbol as follows:
]
APEX_JSON.CLOSE_ARRAY ();
None.
This procedure writes a close curly bracket symbol as follows:
}
APEX_JSON.CLOSE_OBJECT ();
None.
This function determines whether the given path points to an existing value.
APEX_JSON.DOES_EXIST ( p_path IN VARCHAR2, p0 IN VARCHAR2 DEFAULT NULL, p1 IN VARCHAR2 DEFAULT NULL, p2 IN VARCHAR2 DEFAULT NULL, p3 IN VARCHAR2 DEFAULT NULL, p4 IN VARCHAR2 DEFAULT NULL, p_values IN t_values DEFAULT g_values ) RETURN BOOLEAN;
Table 15-1 DOES_EXIST Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
Parsed JSON members. The default is |
Table 15-2 DOES_EXIST Function Returns
| Return | Description |
|---|---|
|
|
Given path points to an existing value. |
|
|
Given path does not point to an existing value |
This example parses a JSON string and prints whether it contains values under a path.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "items": [ 1, 2, { "foo": true } ] }');
if apex_json.does_exist(p_path => 'items[%d].foo', p0 => 3, p_values =>
j) then
dbms_output.put_line('found items[3].foo');
end if;
END;
This function returns paths into p_values that match a given pattern.
APEX_JSON.FIND_PATHS_LIKE (
p_return_path IN VARCHAR2,
p_subpath IN VARCHAR2 DEFAULT NULL,
p_value IN VARCHAR2 DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN wwv_flow_t_varchar2;
Table 15-3 FIND_PATHS_LIKE Function Parameters
| Parameter | Description |
|---|---|
|
|
Search pattern for the return path.. |
|
|
Search pattern under |
|
|
Search pattern for value (optional). |
|
|
Parsed JSON members. The default is |
Table 15-4 FIND_PATHS_LIKE Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
Table of paths that match the pattern. |
|
|
Raises this error if |
This example parses a JSON string, finds paths that match a pattern, and prints the values under the paths.
DECLARE
j apex_json.t_values;
l_paths apex_t_varchar2;
BEGIN
apex_json.parse(j, '{ "items": [ { "name": "Amulet of Yendor", "magical": true }, '||
{ "name": "Slippers", "magical": "rather not" } ]}');
l_paths := apex_json.find_paths_like (
p_values => j,
p_return_path => 'items[%]',
p_subpath => '.magical',
p_value => 'true' );
dbms_output.put_line('Magical items:');
for i in 1 .. l_paths.count loop
dbms_output.put_line(apex_json.get_varchar2(p_values => j, p_path => l_paths(i)||'.name'));
end loop;
END;
Frees output resources. Call this procedure after process if you are using INITIALIZE_CLOB_OUTPUT to write to a temporary CLOB.
free_output;
This example configures APEX_JSON for CLOB output, generate JSON, print the CLOB with DBMS_OUTPUT, and finally free the CLOB.
BEGIN
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('hello', 'world');
apex_json.close_object;
dbms_output.put_line(apex_json.get_clob_output);
apex_json.free_output;
END;
This procedure flushes pending changes. Note that close procedures automatically flush.
APEX_JSON.FLUSH
None.
This example writes incomplete JSON.
BEGIN
apex_json.open_object;
apex_json.write('attr', 'value');
apex_json.flush;
sys.htp.p('the "}" is missing');
END;
This function returns a boolean number value.
APEX_JSON.GET_BOOLEAN (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_default IN BOOLEAN DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN BOOLEAN;
Table 15-5 GET_BOOLEAN Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
The default value if the member does not exist. |
|
|
Parsed JSON members. The default is |
Table 15-6 GET_BOOLEAN Function Returns
| Return | Description |
|---|---|
|
|
.Value at the given path position. |
|
|
Value at the given path position. |
|
|
Value at the given path position. |
|
|
Raises this error if |
This example parses a JSON string and prints the boolean value at a position.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "items": [ 1, 2, { "foo": true } ] }');
if apex_json.get_boolean(p_path=>'items[%d].foo', p0=>3,p_values=>j) then
dbms_output.put_line('items[3].foo is true');
END IF;
END;
Returns the temporary CLOB that you created with INITIALIZE_CLOB_OUTPUT.
function get_clob_output
return clob;
This example configures APEX_JSON for CLOB output, generate JSON, print the CLOB with DBMS_OUTPUT, and finally free the CLOB.
BEGIN
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('hello', 'world');
apex_json.close_object;
dbms_output.put_line(apex_json.get_clob_output);
apex_json.free_output;
END;
This function returns the number of array elements or object members.
APEX_JSON.GET_COUNT (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN NUMBER;
Table 15-7 GET_COUNT Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
Parsed JSON members. The default is |
Table 15-8 GET_COUNT Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
The number of array elements or object members or null if the array or object could not be found |
|
|
Raises this error if |
This example parses a JSON string and prints the number of members at positions.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "foo": 3, "bar": [1, 2, 3, 4] }');
dbms_output.put_line(apex_json.get_count(p_path=>'.',p_values=>j)); -- 2 (foo and bar)
dbms_output.put_line(apex_json.get_count(p_path=>'bar',p_values=>j)); -- 4
END;
This function returns a date member value.
APEX_JSON.GET_DATE (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_default IN DATE DEFAULT NULL,
p_format IN VARCHAR2 DEFAULT c_date_iso8601,
p_values IN t_values DEFAULT g_values )
RETURN DATE;
Table 15-9 GET_DATE Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
The default value if the member does not exist. |
|
|
The date format mask. |
|
|
Parsed JSON members. The default is |
Table 15-10 GET_DATE Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
.Returns the date. |
|
|
Raises this error if |
This example parses a JSON string and prints the value at a position.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "items": [ 1, 2, { "foo": "2014-04-29T10:08:00Z" }] }');
dbms_output.put_line(to_char(apex_json.get_date(p_path=>'items[%d].foo',p0=>3, p_values=>j), 'DD-Mon-YYYY'));
END;
This function returns the table of OBJECT_MEMBERS names for an object.
APEX_JSON.GET_MEMBERS (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN WWV_FLOW_T_VARCHAR2;
Table 15-11 GET_MEMBERS Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
Parsed JSON members. The default is |
Table 15-12 GET_MEMBERS Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
The |
|
|
Raises this error if |
This example parses a JSON string and prints members at positions.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "foo": 3, "bar": [1, 2, 3, 4] }');
dbms_output.put_line(apex_json.get_members(p_path=>'.',p_values=>j)(1)); -- foo
dbms_output.put_line(apex_json.get_members(p_path=>'.',p_values=>j)(2)); -- bar
END;
This function returns a numeric number value.
APEX_JSON.GET_NUMBER (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_default IN BOOLEAN DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN NUMBER;
Table 15-13 GET_NUMBER Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
The default value if the member does not exist. |
|
|
Parsed JSON members. The default is |
Table 15-14 GET_NUMBER Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
The value at the given path position. |
|
|
Raises this error if |
This example parses a JSON string and prints the value at a position.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "items": [ 1, 2, { "foo": 42 } ] }');
dbms_output.put_line(apex_json.get_number(p_path=>'items[%d].foo',p0=> 3,p_values=>j));
END;
This function returns the t_value.
APEX_JSON.GET_VALUE (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN t_value;
Table 15-15 GET_VALUE Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
Parsed JSON members. The default is |
Table 15-16 GET_VALUE Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
The |
|
|
Raises this error if |
This example parses a JSON string and prints attributes of values at positions.
DECLARE
j apex_json.t_values;
v apex_json.t_value;
BEGIN
apex_json.parse(j, '{ "foo": 3, "bar": [1, 2, 3, 4] }');
v := apex_json.get_value(p_path=>'bar[%d]',p0=> 2,p_values=>j); -- returns the t_value for bar[2]
dbms_output.put_line(v.number_value); -- 2
v := apex_json.get_value(p_path=>'does.not.exist',p_values=>j);
dbms_output.put_line(case when v.kind is null then 'not found!' end);
END;
This function returns a varchar2 member value. This function converts boolean and number values to varchar2 values.
APEX_JSON.GET_VARCHAR2 (
p_path IN VARCHAR2,
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_default IN BOOLEAN DEFAULT NULL,
p_values IN t_values DEFAULT g_values )
RETURN VARCHAR2;
Table 15-17 GET_VARCHAR2 Function Parameters
| Parameter | Description |
|---|---|
|
|
Index into |
|
|
Each %N in |
|
|
The default value if the member does not exist. |
|
|
Parsed JSON members. The default is |
Table 15-18 GET_VARCHAR2 Function Returns and Raised Errors
| Return | Description |
|---|---|
|
|
This is the value at the given path position. |
|
|
Raises this error if |
This example parses a JSON string and prints the value at a position.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "items": [ 1, 2, { "foo": 42 } ] }');
dbms_output.put_line(apex_json.get_varchar2(p_path=>'items[%d].foo',p0=> 3,p_values=>j));
END;
Initialize the output interface to write to a temporary CLOB. the default is to write to SYS.HTP. If using CLOB output, you should call FREE_OUTPUT() at the end to free the CLOB.
APEX_JSON.INITIALIZE_CLOB_OUTPUT (
p_dur in pls_integer default sys.dbms_lob.call,
p_cache in boolean default true,
p_indent in pls_integer default null );
Table 15-19 INITIALIZE_CLOB_OUTPUT Procedure Parameters
| Parameter | Description |
|---|---|
|
p_dur |
Duration of the temporary |
|
p_cache |
Specifies if the lob should be read into buffer cache or not. |
|
p_indent |
Indent level. Defaults to 2 if debug is turned on, 0 otherwise. |
This example configures APEX_JSON for CLOB output, generate JSON, print the CLOB with DBMS_OUTPUT, and finally free the CLOB.
BEGIN
apex_json.initialize_clob_output;
apex_json.open_object;
apex_json.write('hello', 'world');
apex_json.close_object;
dbms_output.put_line(apex_json.get_clob_output);
apex_json.free_output;
END;
This procedure initializes the output interface. You only have to call this procedure if you want to modify the parameters below. Initially, output is already configured with the defaults mentioned in the parameter table.
APEX_JSON.INITIALIZE_OUTPUT (
p_http_header in boolean default true,
p_http_cache in boolean default false,
p_http_cache_etag in varchar2 default null,
p_indent in pls_integer default null );
Table 15-20 INITIALIZE_OUTPUT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
If TRUE (the default), write an application/JSON mime type header. |
|
|
This parameter is only relevant if |
|
|
If not null, writes an |
|
|
Indent level. Defaults to 2, if debug is turned on, otherwise defaults to 0. |
This example configures APEX_JSON to not emit default headers, because they are written directly.
BEGIN
apex_json.initialize_output (
p_http_header => false );
sys.owa_util.mime_header('application/json', false);
sys.owa_util.status_line(429, 'Too Many Requests');
sys.owa_util.http_header_close;
--
apex_json.open_object;
apex_json.write('maxRequestsPerSecond', 10);
apex_json.close_object;
END;
This procedure writes an open bracket symbol as follows:
[
APEX_JSON.OPEN_ARRAY (
p_name IN VARCHAR2 DEFAULT NULL );
Table 15-21 OPEN_ARRAY Procedure Parameters
| Parameter | Description |
|---|---|
|
|
If not null, write an object attribute name and colon before the opening bracket. |
This example performs a write { "array":[ 1 ,[ ] ] }.
BEGIN
apex_json.open_object; -- {
apex_json.open_array('array'); -- "array": [
apex_json.write(1); -- 1
apex_json.open_array; -- , [
apex_json.close_array; -- ]
apex_json.close_array; -- ]
apex_json.close_object; -- }
END;
This procedure writes an open curly bracket symbol as follows:
{
APEX_JSON.OPEN_OBJECT (
p_name IN VARCHAR2 DEFAULT NULL );
Table 15-22 OPEN_OBJECT Procedure Parameters
| Parameter | Description |
|---|---|
|
|
If not null, write an object attribute name and colon before the opening brace. |
This example performs a write { "obj": { "obj-attr": "value" }}.
BEGIN
apex_json.open_object; -- {
apex_json.open_object('obj'); -- "obj": {
apex_json.write('obj-attr', 'value'); -- "obj-attr": "value"
apex_json.close_all; -- }}
END;
This procedure parses a JSON-formatted varchar2 or clob and puts the members into p_values.
APEX_JSON.PARSE (
p_values in out nocopy t_values,
p_source in varchar2,
p_strict in boolean default true );
APEX_JSON.PARSE (
p_values in out nocopy t_values,
p_source in clob,
p_strict in boolean default true );
Table 15-23 PARSE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
An index by |
|
|
The JSON source ( |
|
|
If TRUE (default), enforce strict JSON rules |
This example parses JSON and prints member values.
DECLARE
l_values apex_json.t_values;
BEGIN
apex_json.parse (
p_values => l_values,
p_source => '{ "type": "circle", "coord": [10, 20] }' );
sys.htp.p('Point at '||
apex_json.get_number (
p_values => l_values,
p_path => 'coord[1]')||
','||
apex_json.get_number (
p_values => l_values,
p_path => 'coord[2]'));
END;
This procedure parses a JSON-formatted varchar2 or clob and puts the members into the package global g_values. This simplified API works similar to the parse() procedure for signature 1, but saves the developer from declaring a local variable for parsed JSON data and passing it to each JSON API call.
APEX_JSON.PARSE (
p_source IN VARCHAR2,
p_strict IN BOOLEAN DEFAULT TRUE );
APEX_JSON.PARSE (
p_source IN CLOB,
p_strict IN BOOLEAN DEFAULT TRUE );
Table 15-24 PARSE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The JSON source ( |
|
|
If TRUE (default), enforce strict JSON rules. |
This example parses JSON and prints member values.
apex_json.parse('{ "type": "circle", "coord": [10, 20] }');
sys.htp.p('Point at '||
apex_json.get_number(p_path=>'coord[1]')||
','||
apex_json.get_number(p_path=>'coord[2]'));
This function converts a string to an escaped JSON value.
APEX_JSON.STRINGIFY (
p_value IN VARCHAR2 )
RETURN VARCHAR2;
Table 15-26 STRINGIFY Function Returns
| Return | Description |
|---|---|
|
|
The converted and escaped JSON value. |
This example is a query that returns a JSON varchar2 value.
select apex_json.stringify('line 1'||chr(10)||'line 2') from dual;
This function converts a number to an escaped JSON value.
APEX_JSON.STRINGIFY (
p_value IN NUMBER )
RETURN VARCHAR2;
Table 15-28 STRINGIFY Function Returns
| Return | Description |
|---|---|
|
|
The converted and escaped JSON value. |
This example is a query that returns a JSON number value.
select apex_json.stringify(-1/10) from dual
This function converts a date to an escaped JSON value.
APEX_JSON.STRINGIFY (
p_value IN DATE,
p_format IN VARCHAR2 DEFAULT c_date_iso8601 )
RETURN VARCHAR2;
Table 15-29 STRINGIFY Function Parameters
| Parameter | Description |
|---|---|
|
|
The date value to be converted. |
Table 15-30 STRINGIFY Function Returns
| Return | Description |
|---|---|
|
|
The converted and escaped JSON value. |
This example is a query that returns a JSON varchar2 value that is suitable to be converted to dates.
select apex_json.stringify(sysdate) from dual
This function converts a boolean value to an escaped JSON value.
APEX_JSON.STRINGIFY (
p_value IN BOOLEAN,
RETURN VARCHAR2;
Table 15-31 STRINGIFY Function Parameters
| Parameter | Description |
|---|---|
|
|
The boolean value to be converted. |
Table 15-32 STRINGIFY Function Returns
| Return | Description |
|---|---|
|
|
The converted and escaped JSON value. |
This example demonstrates printing JSON boolean values.
BEGIN sys.htp.p(apex_json.stringify(true)); sys.htp.p(apex_json.stringify(false)); END;
This procedure parses a JSON-formatted varchar2 or CLOB and converts it to an xmltype.
APEX_JSON.TO_XMLTYPE (
p_source IN VARCHAR2,
p_strict IN BOOLEAN DEFAULT TRUE )
RETURN sys.xmltype;
APEX_JSON.TO_XMLTYPE (
p_source IN CLOB,
p_strict IN BOOLEAN DEFAULT TRUE )
RETURN sys.xmltype;
Table 15-33 TO_XMLTYPE Function Parameters
| Parameter | Description |
|---|---|
|
|
The JSON source ( |
|
|
If TRUE (default), enforce strict JSON rules |
Table 15-34 TO_XMLTYPE Function Returns
| Return | Description |
|---|---|
|
|
An |
This example parses JSON and prints the XML representation.
DECLARE
l_xml xmltype;
BEGIN
l_xml := apex_json.to_xmltype('{ "items": [ 1, 2, { "foo": true } ] }');
dbms_output.put_line(l_xml.getstringval);
END;
This procedure writes an array attribute of type VARCHAR2.
APEX_JSON.WRITE (
p_value IN VARCHAR2 );
This example writes an array containing 1, "two", "long text", false, the current date and a JSON representation of an xml document.
DECLARE
l_clob clob := 'long text';
l_xml sys.xmltype := sys.xmltype('<obj><foo>1</foo><bar>2</bar></obj>');
BEGIN
apex_json.open_array; -- [
apex_json.write(1); -- 1
apex_json.write('two'); -- , "two"
apex_json.write(l_clob); -- , "long text"
apex_json.write(false); -- , false
apex_json.write(sysdate); -- , "2014-05-05T05:36:08Z"
apex_json.write(l_xml); -- , { "foo": 1, "bar": 2 }
apex_json.close_array; -- ]
END;
This procedure writes an array attribute. of type clob.
APEX_JSON.WRITE (
p_value IN CLOB );
This procedure writes an array attribute of type NUMBER.
APEX_JSON.WRITE (
p_value IN NUMBER );
This procedure writes an array attribute. of type date
APEX_JSON.WRITE (
p_value IN DATE,
p_format IN VARCHAR2 DEFAULT c_date_iso8601 );
This procedure writes an array attribute of type boolean.
APEX_JSON.WRITE (
p_value IN BOOLEAN );
This procedure writes an array attribute of type sys.xmltype. The procedure uses a XSL transformation to generate JSON. To determine the JSON type of values, it uses the following rules:
If the value is empty, it generates a NULL value.
If upper(value) is TRUE, it generates a boolean true value.
If upper(value) is FALSE, it generates a boolean false value.
If the XPath number function returns TRUE, it emits the value as is. Otherwise, it enquotes the value (that is, treats it as a JSON string).
APEX_JSON.WRITE (
p_value IN sys.xmltype );
This procedure writes an array with all rows that the cursor returns. Each row is a separate object. If the query contains object type, collection, or cursor columns, the procedure uses write(xmltype) to generate JSON. Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output. If the column type is varchar2 and the uppercase value is 'TRUE' or 'FALSE', it generates boolean values.
APEX_JSON.WRITE (
p_cursor IN OUT NOCOPY sys_refcursor );
This example writes an array containing JSON objects for departments 10 and 20.
DECLARE
c sys_refcursor;
BEGIN
open c for select deptno, dname, loc from dept where deptno in (10, 20);
apex_json.write(c);
END;
This is the output:
[ { "DEPTNO":10 ,"DNAME":"ACCOUNTING" ,"LOC":"NEW YORK" }
, { "DEPTNO":20 ,"DNAME":"RESEARCH" ,"LOC":"DALLAS" } ]
This procedure writes an object attribute of type VARCHAR2.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN VARCHAR2,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-42 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The attribute value to be written. |
|
|
If true, write |
This example writes an object with named member attributes of various types. The comments to the right of the statements show the output that they generate.
DECLARE
l_clob clob := 'long text';
l_xml sys.xmltype := sys.xmltype('<obj><foo>1</foo><bar>2</bar></obj>');
BEGIN
apex_json.open_object; -- {
apex_json.write('a1', 1); -- "a1": 1
apex_json.write('a2', 'two'); -- ,"a2": "two"
apex_json.write('a3', l_clob); -- ,"a3": "long text"
apex_json.write('a4', false); -- ,"a4": false
apex_json.write('a5', sysdate); -- ,"a5": "2014-05-05T05:36:08Z"
apex_json.write('a6', l_xml); -- ,"a6": { "foo": 1, "bar": 2 }
apex_json.close_object; -- }
END;
This procedure writes an object attribute of type CLOB.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN CLOB,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-43 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The attribute value to be written. |
|
|
If true, write |
See example for "WRITE Procedure Signature 8".
This procedure writes an object attribute of type NUMBER.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN NUMBER,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-44 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The attribute value to be written. |
|
|
If true, write |
See example for "WRITE Procedure Signature 8".
This procedure writes an object attribute of type date.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN DATE,
p_format IN VARCHAR2 DEFAULT c_date_iso8691,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-45 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The attribute value to be written. |
|
|
The date format mask (default |
|
|
If true, write |
See example for "WRITE Procedure Signature 8".
This procedure writes an object attribute of type boolean.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN BOOLEAN,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-46 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The attribute value to be written. |
|
|
If true, write |
See example for "WRITE Procedure Signature 8".
This procedure writes an attribute where the value is an array that contains all rows that the cursor returns. Each row is a separate object.
If the query contains object type, collection, or cursor columns, the procedure uses write(p_name,<xmltype>). See "WRITE Procedure Signature 14." Otherwise, it uses DBMS_SQL to fetch rows and the write() procedures for the appropriate column data types for output. If the column type is varchar2 and the uppercase value is 'TRUE' or 'FALSE', it generates boolean values.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_cursor IN OUT NOCOPY sys_refcursor );
Table 15-47 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
p_name |
The attribute name. |
|
p_cursor |
The cursor. |
This example writes an array containing JSON objects for departments 10 and 20, as an object member attribute.
DECLARE
c sys_refcursor;
BEGIN
open c for select deptno,
dname,
cursor(select empno,
ename
from emp e
where e.deptno=d.deptno) emps
from dept d;
apex_json.open_object;
apex_json. write('departments', c);
apex_json.close_object;
END;
{ "departments":[
{"DEPTNO":10,
"DNAME":"ACCOUNTING",
"EMPS":[{"EMPNO":7839,"ENAME":"KING"}]},
...
,{"DEPTNO":40,"DNAME":"OPERATIONS","EMPS":null}] }
This procedure writes an array attribute of type sys.xmltype. The procedure uses a XSL transformation to generate JSON. To determine the JSON type of values, it uses the following rules:
If the value is empty, it generates a NULL value.
If upper(value) is TRUE, it generates a boolean true value.
If upper(value) is FALSE, it generates a boolean false value.
If the XPath number function returns true, it emits the value as is. Otherwise, it enquotes the value (that is, treats it as a JSON string).
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_value IN sys.xmltype,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-48 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The value to be written. The XML is converted to JSON |
|
|
If true, write |
See example for "WRITE Procedure Signature 13."
This procedure writes parts of a parsed APEX_JSON.t_values table.
APEX_JSON.WRITE (
p_values IN t_values,
p_path IN VARCHAR2 DEFAULT '.',
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL );
Table 15-49 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The parsed JSON members. |
|
|
The index into |
|
|
Each %N in |
This example parses a JSON string and writes parts of it.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "foo": 3, "bar": { "x": 1, "y": 2 }}');
apex_json.write(j,'bar');
END;
This procedure writes parts of a parsed APEX_JSON.t_values table as an object member attribute.
APEX_JSON.WRITE (
p_name IN VARCHAR2,
p_values IN t_values,
p_path IN VARCHAR2 DEFAULT '.',
p0 IN VARCHAR2 DEFAULT NULL,
p1 IN VARCHAR2 DEFAULT NULL,
p2 IN VARCHAR2 DEFAULT NULL,
p3 IN VARCHAR2 DEFAULT NULL,
p4 IN VARCHAR2 DEFAULT NULL,
p_write_null IN BOOLEAN DEFAULT FALSE );
Table 15-50 WRITE Procedure Parameters
| Parameter | Description |
|---|---|
|
|
The attribute name. |
|
|
The parsed JSON members. |
|
|
The index into |
|
|
Each %N in |
|
|
If true, write |
This example parses a JSON string and writes parts of it as an object member.
DECLARE
j apex_json.t_values;
BEGIN
apex_json.parse(j, '{ "foo": 3, "bar": { "x": 1, "y": 2 }}');
apex_json.open_object; -- {
apex_json.write('parsed-bar',j,'bar');-- "parsed-bar":{ "x":1 ,"y":2 }
apex_json.close_object; -- }
END;