A good REST API answers with the right status code and useful headers: 400 for bad input with a JSON message, cache rules for clients, counts or other metadata in headers. ORDS PL/SQL handlers set the status with the :status_code bind and response headers with OUT parameters defined by ORDS.DEFINE_PARAMETER. The body is written with HTP.
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 lounge module from How to Write a POST Handler That Inserts Rows in ORDS.
Syntax
:status_code := 400; -- in the handler
owa_util.mime_header('application/json', true); -- content type of the body
htp.p('...'); -- the body
ords.define_parameter(..., p_name => 'Header-Name', p_bind_variable_name => 'bind',
p_source_type => 'HEADER', p_access_method => 'OUT');A Handler with Its Own Status and Headers
offers/check takes an airport and a price. A missing or non-positive price returns 400 with a JSON error. Otherwise it counts the open offers at the airport, returns them in the body and in an X-Offer-Count header, and tells clients not to cache the answer.
Example:
begin
ords.define_template(p_module_name => 'lounge', p_pattern => 'offers/check');
ords.define_handler(p_module_name => 'lounge',
p_pattern => 'offers/check',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => q'[
declare
v_open pls_integer;
begin
if :price_usd is null or :price_usd <= 0 then
:status_code := 400;
owa_util.mime_header('application/json', true);
htp.p(json_object('error' value 'price_usd must be greater than zero'));
return;
end if;
select count(*) into v_open
from lounge_offers
where airport_code = :airport_code and valid_until >= sysdate;
:offer_count := v_open; -- goes out as the X-Offer-Count header
:cache_control := 'no-store';
:status_code := 200;
owa_util.mime_header('application/json', true);
htp.p(json_object('airport' value :airport_code, 'open_offers' value v_open));
end;]');
ords.define_parameter(p_module_name => 'lounge', p_pattern => 'offers/check',
p_method => 'POST', p_name => 'X-Offer-Count',
p_bind_variable_name => 'offer_count', p_source_type => 'HEADER',
p_param_type => 'INT', p_access_method => 'OUT');
ords.define_parameter(p_module_name => 'lounge', p_pattern => 'offers/check',
p_method => 'POST', p_name => 'Cache-Control',
p_bind_variable_name => 'cache_control', p_source_type => 'HEADER',
p_param_type => 'STRING', p_access_method => 'OUT');
commit;
end;
/A Valid Request
Example:
curl -k -i -X POST https://localhost:8443/ords/nimbus/lounge/offers/check \
-H "Content-Type: application/json" \
-d '{"airport_code":"DXB","price_usd":50}'Output (headers, without the Date header):
HTTP/1.1 200 OK
Content-Type: application/json
Cache-Control: no-store
X-Offer-Count: 1
Transfer-Encoding: chunked
{"airport":"DXB","open_offers":1}The two OUT parameters became the Cache-Control and X-Offer-Count response headers.
A Rejected Request
Example:
curl -k -i -X POST https://localhost:8443/ords/nimbus/lounge/offers/check \
-H "Content-Type: application/json" \
-d '{"airport_code":"DXB","price_usd":0}'Output:
HTTP/1.1 400 Bad Request
Content-Type: application/json
Transfer-Encoding: chunked
{"error":"price_usd must be greater than zero"}The client gets 400 Bad Request and a message it can show, instead of ORDS's generic 555 error.
Things to Know
- Call OWA_UTIL.MIME_HEADER before writing the body, so clients know it is JSON.
- Build JSON with JSON_OBJECT rather than by concatenating strings, so quotes in values are escaped.
- :forward_location sets the Location header and returns the resource it points to, which suits 201 Created.
Related Guides
Conclusion
ORDS PL/SQL handlers control the whole response: :status_code sets the status, OUT parameters with source type HEADER set response headers, and HTP writes the body. Use them to return clear 400 errors and useful headers instead of generic failures.
