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 32KBuild 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
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.
