A GET on /routes/7 should return route 7 as one JSON object, not a collection with one item. ORDS has two source types for that: collection item, which returns the row with a link back to its collection, and query one row, which returns just the row's fields. Both answer 404 Not Found when no row matches. This guide adds both to the network module.
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 examples extend the network module from How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE.
Syntax
ords.define_handler(..., p_source_type => ords.source_type_collection_item, ...); ords.define_handler(..., p_source_type => ords.source_type_query_one_row, ...);
The query should return at most one row, usually by selecting on the primary key from a path parameter.
Collection Item
Example:
begin
ords.define_template(p_module_name => 'network',
p_pattern => 'routes/:id');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes/:id',
p_method => 'GET',
p_source_type => ords.source_type_collection_item,
p_source => 'select route_id, origin, destination, distance_km,
block_minutes
from routes
where route_id = :id');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/routes/7
Output:
{
"route_id": 7,
"origin": "DXB",
"destination": "AMS",
"distance_km": 5168,
"block_minutes": 378,
"links": [
{
"rel": "collection",
"href": "https://localhost:8443/ords/nimbus/network/routes/"
}
]
}Route 7 comes back as an object, with a collection link to the routes collection. A route that does not exist returns 404 Not Found:
Output (HTTP 404 Not Found):
{
"code": "NotFound",
"message": "Not Found",
"type": "tag:oracle.com,2020:error/NotFound",
"instance": "tag:oracle.com,2020:ecid/AJhRWb9fu2ifEcKzGx1J9Q"
}Query One Row
Query one row returns only the selected columns, without links, which suits small computed results.
Example:
begin
ords.define_template(p_module_name => 'network',
p_pattern => 'routes/:id/summary');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes/:id/summary',
p_method => 'GET',
p_source_type => ords.source_type_query_one_row,
p_source => 'select origin || ''-'' || destination as route,
distance_km
from routes
where route_id = :id');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/routes/7/summary
Output:
{
"route": "DXB-AMS",
"distance_km": 5168
}Route 999 again returns 404 Not Found.
Things to Know
- The more specific template wins: routes/:id and routes/from/:origin live side by side in one module.
- Use collection item when clients navigate between items and collections, and query one row for compact answers.
- Column aliases become the JSON field names, in lowercase unless quoted.
Related Guides
- How to Use URI Parameters in ORDS Templates
- How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE
Conclusion
ORDS returns one row as one JSON object with the collection item or query one row source types, and answers 404 when the row does not exist. Pick collection item for linked resources and query one row for plain results.
