How to Return Custom JSON from a PL/SQL Handler

Query-based ORDS handlers shape JSON for you: items, paging, links. When an API needs a document of its own design, a PL/SQL handler can build the JSON with SQL/JSON functions and write it out with HTP. This guide returns an airport summary with a nested list of departures, and handles a missing airport with 404.

Before You Start

You need ORDS installed and running against your database, and a schema to work in. The examples use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder of the Oracle Database 26ai code repository on GitHub. The schema comes from Oracle Database 26ai SQL and PL/SQL Book.

ORDS in the examples answers at https://localhost:8443/ords/, and NIMBUS is REST-enabled with the URL alias nimbus. Replace the host and port with your own ORDS address. The curl commands use -k because the test server has a self-signed certificate; leave it out when your certificate is trusted. JSON responses are formatted for reading; ORDS returns them on one line.

The handler joins the network module from How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE.

Syntax

select json_object('key' value ..., 'list' value (select json_arrayagg(...)) returning clob)
into   v_doc from ...;
owa_util.mime_header('application/json', true);
htp.prn(v_doc);      -- in pieces when the document can exceed 32K

Build and Return the Document

airports/:code/summary builds one JSON document per airport: code, city, hub flag, and its departures ordered by distance. HTP writes at most 32,767 characters per call, so the CLOB is written in 8,000-character pieces.

Example:

begin
  ords.define_template(p_module_name => 'network', p_pattern => 'airports/:code/summary');

  ords.define_handler(p_module_name => 'network',
                      p_pattern     => 'airports/:code/summary',
                      p_method      => 'GET',
                      p_source_type => ords.source_type_plsql,
                      p_source      => q'[
declare
  v_doc clob;
  v_pos pls_integer := 1;
begin
  select json_object(
           'airport'     value a.airport_code,
           'city'        value a.city,
           'hub'         value a.is_hub,
           'departures'  value (select json_arrayagg(
                                         json_object('to' value r.destination,
                                                     'km' value r.distance_km)
                                         order by r.distance_km desc returning clob)
                                from routes r where r.origin = a.airport_code)
           returning clob)
  into   v_doc
  from   airports a
  where  a.airport_code = upper(:code);

  owa_util.mime_header('application/json', true);
  while v_pos <= dbms_lob.getlength(v_doc) loop       -- htp writes up to 32K at a time
    htp.prn(dbms_lob.substr(v_doc, 8000, v_pos));
    v_pos := v_pos + 8000;
  end loop;
exception
  when no_data_found then
    :status_code := 404;
end;]');
  commit;
end;
/

Example:

curl -k https://localhost:8443/ords/nimbus/network/airports/sin/summary

Output:

{
    "airport": "SIN",
    "city": "Singapore",
    "hub": false,
    "departures": [
        {
            "to": "SYD",
            "km": 6294
        },
        {
            "to": "DXB",
            "km": 5845
        },
        {
            "to": "NRT",
            "km": 5358
        }
    ]
}

The document has exactly the shape the handler built, with no items wrapper and no links. The BOOLEAN column is_hub comes out as JSON false. An unknown airport raises NO_DATA_FOUND, which the handler turns into 404:

Output (HTTP 404 Not Found):

{
    "code": "NotFound",
    "message": "Not Found",
    "type": "tag:oracle.com,2020:error/NotFound",
    "instance": "tag:oracle.com,2020:ecid/_fmHw2mkflpYClWaG5VHJQ"
}

Things to Know

• RETURNING CLOB on JSON_OBJECT and JSON_ARRAYAGG avoids the 4,000-byte limit of the default VARCHAR2 result.
• Write with HTP.PRN in pieces for documents that can grow; HTP.P adds a line break after each piece.
• Paging is up to you in a PL/SQL handler; add OFFSET and FETCH FIRST to the query when lists can be long.

Related Guides

• How to Set HTTP Status Codes and Headers in ORDS Handlers
• Oracle JSON_OBJECT Function

Conclusion

A PL/SQL handler returns any JSON shape: build it with JSON_OBJECT and JSON_ARRAYAGG returning CLOB, set the content type, and write it with HTP in pieces. Turn missing rows into 404 so clients can tell them from errors.