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