How to Run Code Before Every Request with an ORDS Pre-Hook

Some rules apply to every REST call: log it, check a maintenance switch, or reject requests from somewhere. Instead of repeating that code in every handler, ORDS can call one PL/SQL function before each REST request: the pre-hook. When it returns TRUE the request goes on; when it returns FALSE, ORDS answers 403 Forbidden. This guide logs every call and adds a maintenance mode.

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.

Setting the pre-hook changes the ORDS configuration and needs an ORDS restart, so try it on a test installation first.

Syntax

create or replace function schema.hook_name return boolean is ... end;

ords config --db-pool default set procedure.rest.preHook schema.hook_name
ords config --db-pool default delete procedure.rest.preHook

Inside the function, OWA_UTIL.GET_CGI_ENV reads request details such as REQUEST_METHOD, SCRIPT_NAME, PATH_INFO, and QUERY_STRING.

The Hook Function

API_GATE writes each request to API_LOG in an autonomous transaction, so the log entry stays even if the request fails, and refuses requests while API_SWITCH says maintenance.

Example:

create table api_log (
  logged_at  timestamp default systimestamp,
  method     varchar2(10),
  path       varchar2(400),
  allowed    varchar2(1)
);
create table api_switch (maintenance varchar2(1) default 'N' not null);
insert into api_switch values ('N');
commit;

create or replace function api_gate return boolean is
  pragma autonomous_transaction;
  v_maintenance api_switch.maintenance%type;
begin
  select maintenance into v_maintenance from api_switch;
  insert into api_log (method, path, allowed)
  values (owa_util.get_cgi_env('REQUEST_METHOD'),
          owa_util.get_cgi_env('SCRIPT_NAME') || owa_util.get_cgi_env('PATH_INFO'),
          case v_maintenance when 'Y' then 'N' else 'Y' end);
  commit;
  return v_maintenance <> 'Y';          -- FALSE stops the request with 403
end;
/

SCRIPT_NAME holds the URL up to the template and PATH_INFO the rest; a first version that logged only PATH_INFO recorded paths such as /7 and /summary.

Register the Hook

Run the ORDS command line with your configuration folder, then restart ORDS:

Example:

ords --config /path/to/ords-config config --db-pool default set procedure.rest.preHook nimbus.api_gate

Output:

The setting named: procedure.rest.preHook was set to: nimbus.api_gate in configuration: default

Calls Are Logged

After the restart, two ordinary calls succeed as before. Then maintenance is switched on, and the next call is refused:

Example:

update api_switch set maintenance = 'Y';
commit;

Output (HTTP 403 Forbidden):

{
    "code": "Forbidden",
    "title": "Forbidden",
    "message": "The user is not authorized to access the requested resource ",
    "type": "tag:oracle.com,2020:error/Forbidden",
    "instance": "tag:oracle.com,2020:ecid/5at2ofxpcz4kFgK9fbqx9w"
}

With maintenance off again, calls succeed. The log shows all four requests:

Example:

select to_char(logged_at, 'HH24:MI:SS') as at, method, path, allowed
from   api_log
order  by logged_at;

Output:

AT          METHOD    PATH                                     ALLOWED
___________ _________ ________________________________________ __________
04:33:09    GET       /ords/nimbus/network/routes/7/summary    Y
04:33:09    GET       /ords/nimbus/airports/                   Y
04:33:11    GET       /ords/nimbus/network/routes/7/summary    N
04:33:13    GET       /ords/nimbus/network/routes/7/summary    Y

Remove the Hook

Example:

ords --config /path/to/ords-config config --db-pool default delete procedure.rest.preHook

Output:

The setting named: procedure.rest.preHook was removed from configuration: default

Restart ORDS again for the change to take effect.

Things to Know

• The hook runs for every REST request of the database pool, so keep it fast, and make sure it never raises an error.
• Every REST-enabled schema must be able to execute the function: grant EXECUTE on it to those schemas.
• Restart ORDS after setting or removing the hook.

Related Guides

• How to Configure ORDS for Oracle APEX
• How to Read Request Data with OWA_UTIL

Conclusion

An ORDS pre-hook runs one PL/SQL function before every REST request: TRUE lets the request through, FALSE returns 403. Use it for logging and global switches, register it with procedure.rest.preHook, restart ORDS, and keep the function fast and error-free.