How to Parse and Generate JSON in PL/SQL Using APEX_JSON

JSON is everywhere in Oracle APEX work: REST responses to read, Ajax callbacks to answer, and payloads to send to other systems. APEX_JSON parses JSON into a table of values you read by path, and writes JSON one value at a time, to a CLOB or straight to the HTTP response. The database has its own JSON support too, and JSON_TABLE, JSON_VALUE, and JSON_OBJECT_T are often the better choice for new SQL-centric code. APEX_JSON is still the simplest way to answer an Ajax callback, and APEX uses it everywhere internally.

This guide covers the whole package with tested examples and their real output, including path placeholders for looping over arrays, dates with time zone offsets, writing whole cursors, and a timestamp format trap in APEX 26.1.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1. Most need nothing but the database: run them as your workspace schema in SQL Developer, SQLcl, SQL*Plus, or SQL Workshop with server output switched on. The output under each example is exactly what the database printed.

Two examples read tables of the Orbit Outfitters sample schema, orb_orders, orb_order_items, orb_products, and orb_categories, which you can install from the orb_tables repository on GitHub. Examples noted as needing a session expect an APEX session of application 200; create one with APEX_SESSION.CREATE_SESSION, as shown in the guide to creating APEX sessions and managing session state from PL/SQL.

Reading JSON

PARSE

Parses JSON, given as a VARCHAR2, a CLOB, or a dbms_sql.varchar2a of chunks, into a table of values of type apex_json.t_values. Without p_values, the result goes into the package variable apex_json.g_values, which every getter reads by default. With p_values, it goes into a variable of your own, which you then pass to the getters. p_strict set to false also accepts unquoted member names and dangling commas; single-quoted strings are rejected either way.

Syntax:

apex_json.parse(p_source in varchar2 | clob | dbms_sql.varchar2a, p_strict in boolean default true)
apex_json.parse(p_values in out nocopy t_values, p_source in varchar2 | clob | dbms_sql.varchar2a,
                p_strict in boolean default true)

A t_values table is indexed by path, such as orderNumber, customer.name, or items[2].sku. Each entry is a t_value record with the fields kind, number_value, varchar2_value, clob_value, and object_members.

Example:

declare
    l_a apex_json.t_values;
    l_b apex_json.t_values;
begin
    -- Two documents parsed into local variables instead of apex_json.g_values
    apex_json.parse(p_values => l_a, p_source => '{"id": 1, "qtys": [5, 10, 20]}');
    apex_json.parse(p_values => l_b, p_source => '{"id": 2, "qtys": [7]}');

    dbms_output.put_line('a: id ' || apex_json.get_number(p_path => 'id', p_values => l_a)
        || ', qtys ' || apex_string.join(apex_json.get_t_number(p_path => 'qtys', p_values => l_a), '+'));
    dbms_output.put_line('b: id ' || apex_json.get_number(p_path => 'id', p_values => l_b));

    -- p_strict => false accepts unquoted member names and dangling commas
    apex_json.parse(p_values => l_a, p_source => '{name: "Tent", price: 249,}', p_strict => false);
    dbms_output.put_line('lax: ' || apex_json.get_varchar2(p_path => 'name', p_values => l_a));

    apex_json.parse(p_values => l_a, p_source => '{name: "Tent", price: 249,}');
exception
    when others then
        dbms_output.put_line('strict: ' || regexp_replace(sqlerrm, '^ORA-\d+: '));
end;
/

Output:

a: id 1, qtys 5+10+20
b: id 2
lax: Tent
strict: Error at line 1, col 2: strict mode JSON parser does not allow unquoted literals

Parsing into your own variables lets you hold several documents at once, which g_values cannot. The last parse fails on purpose, to show strict mode rejecting the unquoted names.

The Getters

Each getter takes a path and returns the value found there. Inside the path, %d and %s are replaced by p0 to p4 in turn, and %0 to %4 by the parameter with that number, which is how you loop over an array. p_default is returned when there is no value at the path, and p_values reads a variable of your own instead of g_values.

Syntax:

apex_json.get_varchar2(p_path in varchar2, p0 .. p4 in varchar2 default null,
                       p_default in varchar2 default null, p_values in t_values default g_values) return varchar2

All getters take p_path, p0 to p4, and p_values:

Example:

declare
    l_order varchar2(4000) := q'~{
      "orderNumber": "ORD-10042",
      "orderDate": "2026-03-14T09:30:00Z",
      "paid": true,
      "customer": { "name": "Alpine Outfitters", "tier": "GOLD" },
      "items": [
        { "sku": "TNT-2P",  "qty": 2, "price": 249.00 },
        { "sku": "BAG-0F",  "qty": 1, "price": 389.50 },
        { "sku": "LMP-HD",  "qty": 4, "price": 39.95 } ],
      "tags": ["priority", "gift"]
    }~';
begin
    apex_json.parse(l_order);   -- fills the package variable apex_json.g_values

    dbms_output.put_line('order:    ' || apex_json.get_varchar2('orderNumber'));
    dbms_output.put_line('date:     ' || to_char(apex_json.get_date('orderDate'), 'DD-MON-YYYY HH24:MI'));
    dbms_output.put_line('paid:     ' || case when apex_json.get_boolean('paid') then 'yes' else 'no' end);
    dbms_output.put_line('customer: ' || apex_json.get_varchar2('customer.name'));
    dbms_output.put_line('items:    ' || apex_json.get_count('items'));

    for i in 1 .. apex_json.get_count('items') loop
        dbms_output.put_line(apex_json.get_varchar2('items[%d].sku', i) || ' x '
            || apex_json.get_number('items[%d].qty', i) || ' @ '
            || apex_json.get_number('items[%d].price', i));
    end loop;

    dbms_output.put_line('tags:     ' || apex_string.join(apex_json.get_t_varchar2('tags'), ', '));
    dbms_output.put_line('members:  ' || apex_string.join(apex_json.get_members('customer'), ', '));
    dbms_output.put_line('coupon?   ' || case when apex_json.does_exist('coupon') then 'yes' else 'no' end);
    dbms_output.put_line('discount: ' || apex_json.get_number('discount', p_default => 0));
end;
/

Output:

order:    ORD-10042
date:     14-MAR-2026 09:30
paid:     yes
customer: Alpine Outfitters
items:    3
TNT-2P x 2 @ 249
BAG-0F x 1 @ 389.5
LMP-HD x 4 @ 39.95
tags:     priority, gift
members:  name, tier
coupon?   no
discount: 0

The loop over items shows the placeholder pattern: 'items[%d].sku' with i as p0. For the SQL equivalent, see the JSON_TABLE function guide.

GET_VALUE_KIND and GET_VALUE let code walk a document it does not know in advance. The path . is the root object:

Example:

declare
    l_members apex_t_varchar2;
    l_value   apex_json.t_value;
begin
    apex_json.parse('{"sku":"TNT-2P","price":249,"active":true,"discontinued":false,'
                 || '"notes":null,"size":{"w":210,"h":130},"colors":["green","sand"]}');
    l_members := apex_json.get_members('.');   -- '.' is the root object
    for i in 1 .. l_members.count loop
        l_value := apex_json.get_value(l_members(i));
        dbms_output.put_line(rpad(l_members(i), 14) ||
            case apex_json.get_value_kind(l_members(i))
                when apex_json.c_null     then 'null'
                when apex_json.c_true     then 'true'
                when apex_json.c_false    then 'false'
                when apex_json.c_number   then 'number   ' || l_value.number_value
                when apex_json.c_varchar2 then 'varchar2 ' || l_value.varchar2_value
                when apex_json.c_object   then 'object   ' || apex_string.join(l_value.object_members, ',')
                when apex_json.c_array    then 'array    ' || apex_json.get_count(l_members(i)) || ' elements'
            end);
    end loop;
end;
/

Output:

sku           varchar2 TNT-2P
price         number   249
active        true
discontinued  false
notes         null
size          object   w,h
colors        array    2 elements

Long Values, Dates, and Time Zones

Dates and timestamps are read with the ISO 8601 format yyyy-mm-dd"T"hh24:mi:ss"Z", fractional seconds included. For a value with an offset such as +05:30, pass a format that includes the offset: the constants apex_json.c_timestamp_iso8601_tzd, or c_timestamp_iso8601_ff_tzd with fractional seconds. c_timestamp_iso8601_tzr and its _ff variant are for region names such as Europe/Berlin.

This example's last call fails on purpose, to show what happens without the format:

Example:

declare
    l_json clob;
begin
    l_json := '{"note":"';
    for i in 1 .. 20 loop
        l_json := l_json || rpad('x', 2000, 'x');           -- 40,000 characters
    end loop;
    l_json := l_json || '","shippedAt":"2026-03-15T16:45:12Z",'
                     || '"deliveredAt":"2026-03-18T11:20:05.250+05:30",'
                     || '"placed":"14.03.2026"}';
    apex_json.parse(l_json);

    dbms_output.put_line('note length:  ' || dbms_lob.getlength(apex_json.get_clob('note')));
    dbms_output.put_line('shipped:      ' || to_char(apex_json.get_timestamp('shippedAt'),
                                                     'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('shipped IST:  ' || to_char(apex_json.get_timestamp('shippedAt',
                                                     p_at_time_zone => 'Asia/Kolkata'), 'DD-MON-YYYY HH24:MI:SS'));
    dbms_output.put_line('delivered:    ' || to_char(apex_json.get_timestamp_tz('deliveredAt',
                                                     p_format => apex_json.c_timestamp_iso8601_ff_tzd),
                                                     'DD-MON-YYYY HH24:MI:SS.FF3 TZH:TZM'));
    dbms_output.put_line('placed:       ' || to_char(apex_json.get_date('placed',
                                                     p_format => 'DD.MM.YYYY'), 'DD-MON-YYYY'));
    -- without p_format the offset +05:30 is read as a region name
    dbms_output.put_line('no format:    ' || to_char(apex_json.get_timestamp_tz('deliveredAt')));
exception
    when others then
        dbms_output.put_line('no format:    ' || regexp_replace(sqlerrm, '^ORA-\d+: '));
end;
/

Output:

note length:  40000
shipped:      15-MAR-2026 16:45:12
shipped IST:  15-MAR-2026 22:15:12
delivered:    18-MAR-2026 11:20:05.250 +05:30
placed:       14-MAR-2026
no format:    time zone region not found

Without an offset format, 26.1 reads +05:30 as a time zone region name and fails with ORA-01882, "time zone region not found". Any API that sends offsets needs the tzd format constant. Note also GET_CLOB returning a 40,000-character value, too long for a VARCHAR2, and p_at_time_zone converting UTC to Indian time.

GeoJSON geometries convert straight into SDO_GEOMETRY:

Example:

declare
    l_geom sdo_geometry;
begin
    apex_json.parse('{"store":"Denver Flagship",'
                 || '"location":{"type":"Point","coordinates":[-104.9903,39.7392]}}');
    l_geom := apex_json.get_sdo_geometry('location');   -- GeoJSON -> SDO_GEOMETRY
    dbms_output.put_line('gtype ' || l_geom.sdo_gtype || ', srid ' || l_geom.sdo_srid
        || ', x ' || l_geom.sdo_point.x || ', y ' || l_geom.sdo_point.y);
end;
/

Output:

gtype 2001, srid 4326, x -104.9903, y 39.7392

FIND_PATHS_LIKE

Returns the paths that match a pattern, as apex_t_varchar2. p_return_path is a LIKE pattern for the paths to return; p_subpath and p_value narrow them to those with a member under that path, and with that value. Use % for any array index.

Syntax:

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 apex_t_varchar2

Example:

declare
    l_paths apex_t_varchar2;
begin
    apex_json.parse('{"items":[{"sku":"TNT-2P","status":"SHIPPED"},'
                 || '{"sku":"BAG-0F","status":"BACKORDER"},'
                 || '{"sku":"LMP-HD","status":"SHIPPED"}]}');

    -- every element of items[] whose status member is SHIPPED
    l_paths := apex_json.find_paths_like(
                   p_return_path => 'items[%]',
                   p_subpath     => '.status',
                   p_value       => 'SHIPPED');
    for i in 1 .. l_paths.count loop
        dbms_output.put_line(l_paths(i) || ' -> ' || apex_json.get_varchar2(l_paths(i) || '.sku'));
    end loop;
end;
/

Output:

items[1] -> TNT-2P
items[3] -> LMP-HD

This is the quickest way to filter array elements by a member's value without writing the loop yourself.

TO_XMLTYPE and TO_XMLTYPE_SQL

Parse JSON and return it as XML: an object becomes elements named after its members, and an array becomes a list of row elements, all under a root element named json. Use TO_XMLTYPE_SQL in SQL, where p_strict is 'Y' or 'N' instead of a Boolean.

Syntax:

apex_json.to_xmltype(p_source in varchar2 | clob | dbms_sql.varchar2a, p_strict in boolean default true) return sys.xmltype
apex_json.to_xmltype_sql(p_source in varchar2 | clob, p_strict in varchar2 default 'Y') return sys.xmltype

Example:

declare
    l_xml xmltype;
begin
    l_xml := apex_json.to_xmltype('{"sku":"TNT-2P","sizes":[2,3]}');
    dbms_output.put_line(l_xml.getclobval());
end;
/

Output:

<?xml version="1.0" encoding="UTF-8"?>
<json><sku>TNT-2P</sku>
<sizes><row>2</row>
<row>3</row>
</sizes>
</json>

In SQL, combined with XMLTABLE, it turns a JSON array into rows:

Example:

select x.sku, x.qty
  from xmltable('/json/items/row'
         passing apex_json.to_xmltype_sql(
                   '{"items":[{"sku":"TNT-2P","qty":2},{"sku":"BAG-0F","qty":1}]}')
         columns sku varchar2(10) path 'sku',
                 qty number       path 'qty') x;

Output:

SKU           QTY
------ ----------
TNT-2P          2
BAG-0F          1

Writing JSON

APEX_JSON writes JSON one step at a time: open an object, write members, close the object. By default the output goes to the HTTP response through the web toolkit's HTP buffer, which is exactly what an Ajax callback needs. Outside a request, write to a CLOB with INITIALIZE_CLOB_OUTPUT and read it back with GET_CLOB_OUTPUT.

INITIALIZE_CLOB_OUTPUT, GET_CLOB_OUTPUT, and FREE_OUTPUT

INITIALIZE_CLOB_OUTPUT sends all output to a temporary CLOB. p_dur is the CLOB's duration (dbms_lob.call by default), p_cache whether it is cached, p_indent the number of spaces per level (none by default), and p_preserve set to true keeps the current output target so FREE_OUTPUT can return to it. GET_CLOB_OUTPUT returns the CLOB, and frees it too when p_free is true; FREE_OUTPUT frees it and returns to the previous output.

Syntax:

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, p_preserve in boolean default false)
apex_json.get_clob_output(p_free in boolean default false) return clob
apex_json.free_output

OPEN_OBJECT, CLOSE_OBJECT, OPEN_ARRAY, CLOSE_ARRAY, and CLOSE_ALL

OPEN_OBJECT and OPEN_ARRAY start an object or array, named with p_name inside an object, or unnamed at the top level or inside an array. CLOSE_OBJECT and CLOSE_ARRAY end the innermost one, and CLOSE_ALL ends everything still open.

Syntax:

apex_json.open_object(p_name in varchar2 default null)
apex_json.open_array(p_name in varchar2 default null)

WRITE

Writes a value: with p_name, a member of the current object; without it, an element of the current array. More than twenty overloads cover the data types, and named null values are left out unless p_write_null is true.

Example:

begin
    apex_json.initialize_clob_output(p_indent => 2);   -- write to a CLOB, 2-space indents

    apex_json.open_object;                                  -- {
    apex_json.write('orderNumber', 'ORD-10042');
    apex_json.write('total',       1047.30);
    apex_json.write('orderDate',   date '2026-03-14');
    apex_json.write('paid',        true);
    apex_json.write('coupon',      cast(null as varchar2));  -- omitted
    apex_json.write('notes',       cast(null as varchar2), p_write_null => true);
    apex_json.write('tags',        apex_t_varchar2('priority', 'gift'));
    apex_json.open_object('customer');                      -- "customer": {
    apex_json.write('name', 'Alpine Outfitters');
    apex_json.close_object;                                 -- }
    apex_json.open_array('qtys');                           -- "qtys": [
    apex_json.write(2);
    apex_json.write(1);
    apex_json.close_array;                                  -- ]
    apex_json.close_all;                                    -- closes the root object

    dbms_output.put_line(apex_json.get_clob_output);
    apex_json.free_output;
end;
/

Output:

{
  "orderNumber":"ORD-10042"
 ,"total":1047.3
 ,"orderDate":"2026-03-14T00:00:00Z"
 ,"paid":true
 ,"notes":null
 ,"tags":[
    "priority"
   ,"gift"
  ]
 ,"customer":{
    "name":"Alpine Outfitters"
  }
 ,"qtys":[
    2
   ,1
  ]
}

The null coupon was left out, while notes appeared as null because of p_write_null. The leading commas are simply how APEX_JSON indents; the result is valid JSON.

A cursor writes a whole result set, nested cursors included. Name the columns in double quotes to control the case of the member names; unquoted names come out in upper case, like SKU here. This example reads the sample schema's orb_orders, orb_order_items, and orb_products tables.

Example:

declare
    l_orders sys_refcursor;
begin
    open l_orders for
        select o.order_number as "orderNumber",
               o.status       as "status",
               o.order_total  as "total",
               cursor(select p.sku, i.quantity as "qty"
                        from orb_order_items i
                        join orb_products p on p.product_id = i.product_id
                       where i.order_id = o.order_id
                       order by p.sku) as "items"
          from orb_orders o
         where o.order_id in (1, 2)
         order by o.order_id;

    apex_json.initialize_clob_output(p_indent => 1);
    apex_json.open_object;
    apex_json.write('orders', l_orders);   -- one object per row, nested cursor -> array
    apex_json.close_object;
    dbms_output.put_line(apex_json.get_clob_output(p_free => true));
end;
/

Output:

{
 "orders":[{"orderNumber":"ORD-10001","status":"CANCELLED","total":35164.61,"items":[{"SKU":"BPK-1003","qty":33},{"SKU":"GPS-1002","qty":17},{"SKU":"GPS-1003","qty":23},{"SKU":"SLP-1005","qty":7},{"SKU":"TNT-1001","qty":18},{"SKU":"TNT-1003","qty":17}]},{"orderNumber":"ORD-10002","status":"DELIVERED","total":44.99,"items":[{"SKU":"TRV-1004","qty":1}]}]
}

A parsed document, or part of one, can be copied into the output:

Example:

declare
    l_in apex_json.t_values;
begin
    apex_json.parse(l_in, '{"order":{"no":"ORD-10042","items":[{"sku":"TNT-2P","qty":2},'
                       || '{"sku":"BAG-0F","qty":1}]},"audit":{"by":"SYNC"}}');

    apex_json.initialize_clob_output(p_indent => 1);
    apex_json.open_object;
    apex_json.write('source', 'webshop');
    apex_json.write('lines', l_in, 'order.items');          -- copy a parsed sub-tree
    apex_json.write('first', l_in, 'order.items[%d]', 1);   -- p0 fills %d
    apex_json.close_object;
    dbms_output.put_line(apex_json.get_clob_output(p_free => true));
end;
/

Output:

{
 "source":"webshop"
,"lines":[
  {
   "sku":"TNT-2P"
  ,"qty":2
  }
 ,{
   "sku":"BAG-0F"
  ,"qty":1
  }
 ]
,"first":{
  "sku":"TNT-2P"
 ,"qty":2
 }
}

And the less common types:

Example:

begin
    apex_json.initialize_clob_output(p_indent => 1);
    apex_json.open_object;
    apex_json.write('placed',   timestamp '2026-03-14 09:30:00');
    apex_json.write('shipped',  timestamp '2026-03-15 16:45:12 +05:30');
    apex_json.write('due',      date '2026-03-20', p_format => 'DD.MM.YYYY');
    apex_json.write('qtys',     apex_t_number(2, 1, 4));
    apex_json.write('label',    apex_util.clob_to_blob('ORBIT'));           -- BLOB -> Base64
    apex_json.write('location', sdo_geometry(2001, 4326,
                                   sdo_point_type(-104.9903, 39.7392, null), null, null));
    apex_json.write('dims',     xmltype('<dims><w>210</w><h>130</h></dims>'));
    apex_json.close_object;
    dbms_output.put_line(apex_json.get_clob_output(p_free => true));
end;
/

Output:

{
 "placed":"2026-03-14T09:30:00.000000000Z"
,"shipped":"2026-03-15T16:45:12.000000000+05:30"
,"due":"20.03.2026"
,"qtys":[
  2
 ,1
 ,4
 ]
,"label":"T1JCSVQ="
,"location":{ "type": "Point", "coordinates": [-104.9903, 39.7392] },"dims":{"w":210,"h":130}
}

In 26.1, an SDO_GEOMETRY value is written on one line regardless of p_indent, and the next member follows on the same line. The JSON is valid, just not pretty. For generating JSON in SQL instead, see generating JSON output with a SQL query.

WRITE_CONTEXT

Writes the rows of an APEX_EXEC query context as an array of objects, so JSON can come from any data source APEX can query, including REST data sources. VARCHAR2 values 'TRUE' and 'FALSE' become Booleans. It needs an APEX session.

Syntax:

apex_json.write_context(p_name in varchar2 default null, p_context in apex_exec.t_context,
                        p_write_null in boolean default false)

This example needs a session of application 200, page 1, and reads the sample schema's orb_categories table.

Example:

declare
    l_context apex_exec.t_context;
begin
    l_context := apex_exec.open_query_context(
                     p_location  => apex_exec.c_location_local_db,
                     p_sql_query => 'select category_id, category_name
                                       from orb_categories
                                      where parent_category_id is null
                                      order by category_id
                                      fetch first 2 rows only');
    apex_json.initialize_clob_output(p_indent => 1);
    apex_json.open_object;
    apex_json.write_context(p_name => 'categories', p_context => l_context);
    apex_json.close_object;
    dbms_output.put_line(apex_json.get_clob_output(p_free => true));
    apex_exec.close(l_context);
end;
/

Output:

{
 "categories":[
  {
   "CATEGORY_ID":1
  ,"CATEGORY_NAME":"Camping"
  }
 ,{
   "CATEGORY_ID":2
  ,"CATEGORY_NAME":"Hiking"
  }
 ,{
   "CATEGORY_ID":3
  ,"CATEGORY_NAME":"Clothing"
  }
 ]
}

Look closely at the result: three categories came back although the query says fetch first 2 rows only. The row limit written in the query text did not take effect through the query context in this run, so limit rows with APEX_EXEC's own paging parameters when the count matters.

INITIALIZE_OUTPUT and FLUSH

INITIALIZE_OUTPUT sends output to HTP, the default, and, with p_http_header true (also the default), writes the HTTP header first: the content type application/json plus APEX's security headers. p_http_cache and p_http_cache_etag let the browser cache the response, and p_indent indents it. FLUSH writes buffered output to the target. In an Ajax Callback process you usually need neither, because APEX has already set up the output and a plain open_object to close_object answers the request.

Syntax:

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)
apex_json.flush

This example needs a session of application 200, page 1. It sets up a fake web request so the HTP buffer can be read back in a script.

Example:

declare
    l_page htp.htbuf_arr;
    l_rows integer := 999;
    l_name owa.vc_arr;
    l_val  owa.vc_arr;
begin
    -- SQL*Plus has no web request; set one up the way ORDS does
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';
    owa.init_cgi_env(1, l_name, l_val);
    htp.init;

    -- What an Ajax Callback process does: write straight to the HTTP response (HTP)
    apex_json.initialize_output(p_indent => 1);   -- sends a JSON content-type header
    apex_json.open_object;
    apex_json.write('status', 'ok');
    apex_json.write('count', 3);
    apex_json.close_object;
    apex_json.flush;                   -- push buffered output to HTP

    -- read the HTP buffer back to show what the browser receives
    owa.get_page(l_page, l_rows);
    for i in 1 .. l_rows loop
        dbms_output.put(l_page(i));
    end loop;
    dbms_output.new_line;
end;
/

Output:

Content-Type: application/json
X-Content-Type-Options: nosniff
X-Xss-Protection: 1; mode=block
Referrer-Policy: strict-origin
Content-Security-Policy: default-src 'self' 'nonce-5R7p89FQd9oPIhgelaSm8w'; script-src 'self' 'nonce-5R7p89FQd9oPIhgelaSm8w' 'wasm-unsafe-eval'; connect-src 'self' https://elocation.oracle.com; img-src 'self' data: blob: https://elocation.oracle.com; font-src 'self' data:; worker-src 'self' blob:; object-src 'none'; frame-ancestors 'self';
Cache-Control: no-store
Pragma: no-cache
Expires: Sun, 27 Jul 1997 13:00:00 GMT
Content-length: 30

{
 "status":"ok"
,"count":3
}

This is exactly what the browser receives from an Ajax callback, security headers included. Calling such a callback from JavaScript is covered in the guide to calling the server with apex.server.process.

STRINGIFY and TO_MEMBER_NAME

STRINGIFY returns one value as JSON text: a quoted and escaped string, a number, an ISO 8601 date or timestamp, true or false, or GeoJSON for an SDO_GEOMETRY as a CLOB. It suits code that assembles JSON by hand. TO_MEMBER_NAME returns a member name, quoted only when it has to be, for JavaScript objects.

Syntax:

apex_json.stringify(p_value in varchar2 | number | boolean) return varchar2
apex_json.stringify(p_value in date | timestamp | timestamp with time zone, p_format in varchar2 default ..., ...) return varchar2
apex_json.to_member_name(p_string in varchar2) return varchar2

Example:

begin
    dbms_output.put_line(apex_json.stringify('Tent "Ridge"' || chr(10) || 'Ultralight'));
    dbms_output.put_line(apex_json.stringify(249.5));
    dbms_output.put_line(apex_json.stringify(date '2026-03-14'));
    dbms_output.put_line(apex_json.stringify(timestamp '2026-03-14 09:30:00.5 +05:30'));
    dbms_output.put_line(apex_json.stringify(true));
    dbms_output.put_line(apex_json.to_member_name('unit price'));
    dbms_output.put_line(apex_json.to_member_name('sku'));
end;
/

Output:

"Tent \"Ridge\"\nUltralight"
249.5
"2026-03-14T00:00:00Z"
"2026-03-14T09:30:00.500000000+05:30"
true
"unit price"
sku

Conclusion

APEX_JSON.PARSE turns JSON into a table of values that the getters read by path, with %d placeholders for looping over arrays, GET_VALUE_KIND for unknown documents, and FIND_PATHS_LIKE for filtering. Read offset timestamps with the tzd format constants, or 26.1 fails with ORA-01882. On the writing side, open and close objects and arrays around WRITE calls, write whole cursors with nested arrays in one call, copy parsed sub-trees, and send the result to a CLOB or straight to the HTTP response of an Ajax callback. For SQL-heavy work, the database's JSON_TABLE and JSON_OBJECT_T are good companions; for Ajax callbacks and quick PL/SQL JSON, APEX_JSON remains the simplest tool.