Creating a resource through a REST API means a POST with a JSON body. In an ORDS PL/SQL handler, every field of the JSON body arrives as a bind variable of the same name, so an INSERT can use :airport_code or :title directly. The handler then sets the response: 201 Created, and the new row, by forwarding to the URL that returns it.
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 inserts into LOUNGE_OFFERS, a small table created in How to REST-Enable a Table with ORDS AutoREST.
Syntax
ords.define_handler(p_module_name => 'module',
p_pattern => 'resource',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => 'begin
insert ... values (:field1, :field2, ...);
:status_code := 201;
:forward_location := ''resource/'' || new_id;
end;');:status_code sets the HTTP status, and :forward_location makes ORDS answer with the resource at that URL, relative to the module.
The Lounge Module
The lounge module gets a GET handler for one offer and a POST handler that creates offers.
Example:
begin
ords.define_module(p_module_name => 'lounge',
p_base_path => '/lounge/',
p_status => 'PUBLISHED');
-- read one offer: the POST handler forwards here
ords.define_template(p_module_name => 'lounge', p_pattern => 'offers/:id');
ords.define_handler(p_module_name => 'lounge',
p_pattern => 'offers/:id',
p_method => 'GET',
p_source_type => ords.source_type_collection_item,
p_source => 'select offer_id, airport_code, title, price_usd, valid_until
from lounge_offers
where offer_id = :id');
-- create an offer from the JSON body
ords.define_template(p_module_name => 'lounge', p_pattern => 'offers');
ords.define_handler(p_module_name => 'lounge',
p_pattern => 'offers',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => q'[
declare
v_id lounge_offers.offer_id%type;
begin
insert into lounge_offers (airport_code, title, price_usd, valid_until)
values (:airport_code, :title, :price_usd, to_date(:valid_until, 'YYYY-MM-DD'))
returning offer_id into v_id;
:status_code := 201;
:forward_location := 'offers/' || v_id;
end;]');
commit;
end;
/Create an Offer
Example:
curl -k -X POST https://localhost:8443/ords/nimbus/lounge/offers \
-H "Content-Type: application/json" \
-d '{"airport_code":"NRT","title":"Ramen bar access","price_usd":22,"valid_until":"2026-12-31"}'Output (HTTP 201 Created):
{
"offer_id": 21,
"airport_code": "NRT",
"title": "Ramen bar access",
"price_usd": 22,
"valid_until": "2026-12-31T00:00:00Z",
"links": [
{
"rel": "collection",
"href": "https://localhost:8443/ords/nimbus/lounge/offers/"
}
]
}The body is the new offer, read through the GET handler that :forward_location pointed to. The identity column generated 21: identity values are not guaranteed to be consecutive.
The response headers include a Location header with the new offer's URL (shown here for a second offer; the Date header is left out):
Output:
HTTP/1.1 201 Created
Content-Type: application/json
Content-Location: https://localhost:8443/ords/nimbus/lounge/offers/23
ETag: "P5zixfN1SMw+SbclXDoKH4jApJ2nHva5HYPY6oQzRf08oOc0mCz+UN33DAoUvi/jjihcndtQRH/Wb00y244M5g=="
Location: https://localhost:8443/ords/nimbus/lounge/offers/23
Transfer-Encoding: chunked
{"offer_id":23,"airport_code":"SIN","title":"Nap pod, 2 hours","price_usd":30,"valid_until":"2026-12-31T00:00:00Z","links":[{"rel":"collection","href":"https://localhost:8443/ords/nimbus/lounge/offers/"}]}When the Insert Fails
A body without a title breaks the NOT NULL constraint. The PL/SQL error is not shown to the client; ORDS answers with status 555:
Example:
curl -k -X POST https://localhost:8443/ords/nimbus/lounge/offers \
-H "Content-Type: application/json" \
-d '{"airport_code":"NRT","price_usd":22}'Output (HTTP 555):
{
"code": "UserDefinedResourceError",
"title": "User Defined Resource Error",
"message": "The request could not be processed for a user defined resource",
"o:errorCode": "ORDS-25001",
"action": "Verify that the URI and payload are correctly specified for the requested operation. If the issue persists then please contact the author of the resource",
"type": "tag:oracle.com,2020:error/UserDefinedResourceError",
"instance": "tag:oracle.com,2020:ecid/h8T9ISV0XtzpB1-S5g8p8g"
}To return a clear 400 with your own message instead, check the input in the handler, as shown in How to Use URI Parameters in ORDS Templates for parameter types, or set :status_code yourself.
Things to Know
- JSON field names map to bind names; a field that is missing from the body binds as NULL.
- Dates arrive as text: convert them with TO_DATE and an explicit format.
- ORDS commits the handler's work when it completes without error, and rolls it back on an error.
- Send Content-Type: application/json, or the body fields are not bound.
Related Guides
Conclusion
An ORDS POST handler reads the JSON body through bind variables, inserts the row, sets :status_code to 201, and forwards to the new resource with :forward_location, which also sets the Location header. Validate input so clients get clear errors rather than 555.
