How to Use URI Parameters in ORDS Templates

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.