How to Write PUT and DELETE Handlers in ORDS

A complete REST resource can be changed and removed, not only read and created. In ORDS, a template can have one handler per HTTP method, so the URL of one offer can answer GET, PUT, and DELETE. This guide adds PUT and DELETE handlers that update and delete lounge offers, and return 404 when the offer does not exist.

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 handlers join the lounge module from How to Write a POST Handler That Inserts Rows in ORDS, whose offers/:id template already has a GET handler.

Syntax

ords.define_handler(p_module_name => 'module', p_pattern => 'resource/:id',
                    p_method => 'PUT',    p_source_type => ords.source_type_plsql,
                    p_source => '...');
ords.define_handler(p_module_name => 'module', p_pattern => 'resource/:id',
                    p_method => 'DELETE', p_source_type => ords.source_type_plsql,
                    p_source => '...');

In the handlers, :id is the path value and the JSON body fields are binds, as in a POST handler. SQL%ROWCOUNT tells whether a row was found.

Define the Handlers

Example:

begin
  ords.define_handler(p_module_name => 'lounge',
                      p_pattern     => 'offers/:id',
                      p_method      => 'PUT',
                      p_source_type => ords.source_type_plsql,
                      p_source      => q'[
begin
  update lounge_offers
  set    title       = :title,
         price_usd   = :price_usd,
         valid_until = to_date(:valid_until, 'YYYY-MM-DD')
  where  offer_id = :id;
  if sql%rowcount = 0 then
    :status_code := 404;
  else
    :forward_location := :id;            -- return the updated offer
  end if;
end;]');

  ords.define_handler(p_module_name => 'lounge',
                      p_pattern     => 'offers/:id',
                      p_method      => 'DELETE',
                      p_source_type => ords.source_type_plsql,
                      p_source      => q'[
begin
  delete from lounge_offers where offer_id = :id;
  :status_code := case sql%rowcount when 0 then 404 else 204 end;
end;]');
  commit;
end;
/

PUT updates the offer and forwards to it, so the response is the updated row; with no row it sets 404. DELETE answers 204 No Content, or 404.

Update an Offer

Example:

curl -k -X PUT https://localhost:8443/ords/nimbus/lounge/offers/23 \
  -H "Content-Type: application/json" \
  -d '{"title":"Nap pod, 3 hours","price_usd":40,"valid_until":"2027-01-31"}'

Output (HTTP 200 OK):

{
    "offer_id": 23,
    "airport_code": "SIN",
    "title": "Nap pod, 3 hours",
    "price_usd": 40,
    "valid_until": "2027-01-31T00:00:00Z",
    "links": [
        {
            "rel": "collection",
            "href": "https://localhost:8443/ords/nimbus/lounge/offers/"
        }
    ]
}

The same request for offer 999 returns 404 Not Found.

Delete an Offer

Example:

curl -k -X DELETE https://localhost:8443/ords/nimbus/lounge/offers/23

The response is HTTP 204 No Content, with an empty body. Deleting offer 23 a second time finds no row and returns 404:

Output (HTTP 404 Not Found):

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

Things to Know

• PUT should replace the resource, so send all its fields; to allow partial changes, write each column as NVL(:field, column).
• DELETE handlers should be idempotent in effect: a second delete changes nothing and reports 404.
• Protect write methods: anyone who can reach the URL can call them until a privilege covers it.

Related Guides

• How to Write a POST Handler That Inserts Rows in ORDS
• How to Return a Single Row from an ORDS Handler

Conclusion

PUT and DELETE handlers complete an ORDS resource: update or delete by the path parameter, check SQL%ROWCOUNT, and set :status_code to 404 when nothing matched. Return the updated row with :forward_location, and 204 after a delete.