How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE

AutoREST publishes tables as they are. For an API shaped your way, with your own URLs and your own SQL, ORDS uses REST modules. A module groups related endpoints under a base path, a template is a URL pattern inside it, and a handler is the code that answers one HTTP method on that template. This guide builds a module for the Nimbus Air route network with one GET endpoint.

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.

Syntax

ords.define_module(p_module_name    => 'name',
                   p_base_path      => '/base/',
                   p_items_per_page => 25,
                   p_status         => 'PUBLISHED');

ords.define_template(p_module_name => 'name', p_pattern => 'resource');

ords.define_handler(p_module_name => 'name',
                    p_pattern     => 'resource',
                    p_method      => 'GET',             -- or POST, PUT, DELETE
                    p_source_type => ords.source_type_collection_feed,
                    p_source      => 'select ...');

The endpoint answers at https://host:port/ords/schema_alias/base/resource. ORDS.DEFINE_SERVICE creates a module, template, and handler in one call.

Define the Module

The network module has the base path /network/ and 10 rows per page. Its routes template gets a GET handler that lists routes as a collection.

Example:

begin
  ords.define_module(p_module_name    => 'network',
                     p_base_path      => '/network/',
                     p_items_per_page => 10,
                     p_status         => 'PUBLISHED',
                     p_comments       => 'Nimbus Air route network');

  ords.define_template(p_module_name => 'network',
                       p_pattern     => 'routes');

  ords.define_handler(p_module_name    => 'network',
                      p_pattern        => 'routes',
                      p_method         => 'GET',
                      p_source_type    => ords.source_type_collection_feed,
                      p_source         => 'select route_id, origin, destination, distance_km
                                           from   routes
                                           order  by route_id');
  commit;
end;
/

Check the Definition

The USER_ORDS_ views show modules, templates, and handlers, joined by their IDs.

Example:

select m.name as module, m.uri_prefix, t.uri_template, h.method, h.source_type
from   user_ords_modules m
join   user_ords_templates t on t.module_id = m.id
join   user_ords_handlers  h on h.template_id = t.id
where  m.name = 'network';

Output:

MODULE     URI_PREFIX    URI_TEMPLATE    METHOD    SOURCE_TYPE
__________ _____________ _______________ _________ __________________
network    /network/     routes          GET       json/collection

The handler's source type json/collection is the stored name of ORDS.SOURCE_TYPE_COLLECTION_FEED.

Call the Endpoint

Example:

curl -k "https://localhost:8443/ords/nimbus/network/routes?limit=3"

Output:

{
    "items": [
        {
            "route_id": 1,
            "origin": "DXB",
            "destination": "LHR",
            "distance_km": 5497
        },
        {
            "route_id": 2,
            "origin": "LHR",
            "destination": "DXB",
            "distance_km": 5497
        },
        {
            "route_id": 3,
            "origin": "DXB",
            "destination": "CDG",
            "distance_km": 5239
        }
    ],
    "hasMore": true,
    "limit": 3,
    "offset": 0,
    "count": 3,
    "links": [
        {
            "rel": "self",
            "href": "https://localhost:8443/ords/nimbus/network/routes"
        },
        {
            "rel": "describedby",
            "href": "https://localhost:8443/ords/nimbus/metadata-catalog/network/item"
        },
        {
            "rel": "first",
            "href": "https://localhost:8443/ords/nimbus/network/routes?limit=3"
        },
        {
            "rel": "next",
            "href": "https://localhost:8443/ords/nimbus/network/routes?offset=3&limit=3"
        }
    ]
}

The query's columns become JSON fields in lowercase, and ORDS adds paging and links, as for AutoREST. Without limit, a page holds the module's 10 rows.

Methods Without a Handler

The template has only a GET handler, so a POST is refused:

Output (HTTP 405 Method Not Allowed):

{
    "code": "MethodNotAllowed",
    "message": "Method Not Allowed",
    "type": "tag:oracle.com,2020:error/MethodNotAllowed",
    "instance": "tag:oracle.com,2020:ecid/R03dQyRjzplYsK5vXCDrNg"
}

Things to Know

• ORDS.DEFINE_MODULE and ORDS.DEFINE_TEMPLATE replace an existing module or template, including everything under it; define handlers again after redefining a template.
• Calling ORDS.DEFINE_HANDLER again for the same template and method replaces just that handler.
• A module with status NOT_PUBLISHED keeps its definition but answers 404 until it is published.
• Commit after the calls; they are ordinary database changes.

Related Guides

• How to REST-Enable a Table with ORDS AutoREST
• Publishing REST Services with ORDS and Oracle APEX

Conclusion

REST modules give you full control over ORDS APIs: a module sets the base path, templates set the URLs, and handlers run your SQL or PL/SQL for each method. Define them with the ORDS package, check them in the USER_ORDS_ views, and call them like any REST endpoint.