Most API calls carry values: an airport code in the path, a minimum distance in the query string. ORDS turns both into bind variables. A :name in a template pattern matches one path segment, and query-string parameters are available under their own names. This guide adds a parameterized endpoint to the network module and shows how to declare a parameter's type so bad input gets a clear error.
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_template(p_module_name => 'module', p_pattern => 'resource/:param');
-- in the handler source: :param for the path value, :name for ?name=value
ords.define_parameter(p_module_name => 'module',
p_pattern => 'resource/:param',
p_method => 'GET',
p_name => 'name', -- name in the request
p_bind_variable_name => 'name', -- name in the SQL
p_source_type => 'URI', -- or HEADER, RESPONSE
p_param_type => 'INT', -- STRING, INT, DOUBLE, BOOLEAN, ...
p_access_method => 'IN');A Path Parameter and a Query Parameter
routes/from/:origin lists the routes from an airport, longest first. The optional min_km in the query string sets a minimum distance.
Example:
begin
ords.define_template(p_module_name => 'network',
p_pattern => 'routes/from/:origin');
ords.define_handler(p_module_name => 'network',
p_pattern => 'routes/from/:origin',
p_method => 'GET',
p_source_type => ords.source_type_collection_feed,
p_source => 'select route_id, destination, distance_km
from routes
where origin = upper(:origin)
and distance_km >= nvl(to_number(:min_km), 0)
order by distance_km desc');
commit;
end;
/Example:
curl -k https://localhost:8443/ords/nimbus/network/routes/from/SIN
Output:
{
"items": [
{
"route_id": 43,
"destination": "SYD",
"distance_km": 6294
},
{
"route_id": 28,
"destination": "DXB",
"distance_km": 5845
},
{
"route_id": 45,
"destination": "NRT",
"distance_km": 5358
}
],
"hasMore": false,
"limit": 10,
"offset": 0,
"count": 3,
"links": [
{
"rel": "self",
"href": "https://localhost:8443/ords/nimbus/network/routes/from/SIN"
},
{
"rel": "describedby",
"href": "https://localhost:8443/ords/nimbus/metadata-catalog/network/routes/from/item"
},
{
"rel": "first",
"href": "https://localhost:8443/ords/nimbus/network/routes/from/SIN"
}
]
}Both parameters arrive as text: UPPER makes dxb match DXB, and TO_NUMBER turns min_km into a number. With ?min_km=10000 the same endpoint returns the 7 routes from Dubai longer than 10,000 km, and an unknown airport returns an empty items list, not an error.
Bad Input Without a Declared Type
A min_km that is not a number fails inside the query, and ORDS hides the database error behind status 555:
Example:
curl -k "https://localhost:8443/ords/nimbus/network/routes/from/DXB?min_km=far"
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/-0noPHe1gE7M80UkLTLahg"
}Declare the Parameter Type
Declaring min_km as INT makes ORDS check the value before running the query.
Example:
begin
ords.define_parameter(p_module_name => 'network',
p_pattern => 'routes/from/:origin',
p_method => 'GET',
p_name => 'min_km',
p_bind_variable_name => 'min_km',
p_source_type => 'URI',
p_param_type => 'INT',
p_access_method => 'IN');
commit;
end;
/The same request now gets 400 Bad Request, with a message that names the parameter:
Output (HTTP 400 Bad Request):
{
"code": "BadRequest",
"title": "Bad Request",
"message": "Invalid value provided for parameter min_km. The detailed error message: Character f is neither a decimal digit number, decimal point, nor \"e\" notation exponential mark..",
"type": "tag:oracle.com,2020:error/BadRequest",
"instance": "tag:oracle.com,2020:ecid/D1fTD18yS-Pjt2T1CBSOdw"
}Things to Know
- A path parameter matches one segment: routes/from/:origin does not match routes/from/a/b.
- Query-string parameters need no declaration to be used as binds; declare them to check their type or to document them.
- Parameter values are always binds, never pasted into the SQL text, so they cannot inject SQL.
Related Guides
- How to Create a REST Module, Template, and Handler with ORDS.DEFINE_MODULE
- How to Prevent SQL Injection with DBMS_ASSERT in PL/SQL
Conclusion
ORDS passes path segments named in the template, and query-string values, to handlers as bind variables. Convert them as needed in SQL, and declare a parameter's type with ORDS.DEFINE_PARAMETER so wrong input is rejected with a clear 400 instead of a 555.
