REST: Don't Let Them Wait

REST: Don’t Let Them Wait

I have been working with Oracle REST Data Services (ORDS) lately and have observed others struggling with third-party REST APIs from the sideline. Many things need to be done right, but speed is critical. Some systems are susceptible to latency, especially if the call to your API is just one piece of a more comprehensive workflow. Spare yourself from the hassle when people call and complain about timeouts.

The short answer is to do it asynchronously. Asynchronous means the client does not wait for you to finish processing the request. Instead, the code behind your API submits a job that runs independently in the database after validation and other necessary processing. The client can poll for the status of the request later. (The response to the POST request can include the self-link that simplifies this.)

If you work with ORDS, the solution is easy. Here is a simple example of how to do that:

begin
   DBMS_SCHEDULER.CREATE_JOB (
   job_name    => 'process_42',
   job_type    => 'PLSQL_BLOCK',
   job_action  => 'begin stuff.process(p_lotsofwork); end; ',
   enabled     => true);
end;
/

First of all, the database user needs the CREATE JOB privilege. When submitted like this, the database will drop the job after completion, so there is no need to clean up later.

One important note is that a call to this procedure has an implicit commit, so you should put it at the end of your routine and after all validations, any previous work not committed will be after the call.

Here is a link to the documentation (version 19c) for more details.

Simple requests should return immediately. If the query takes too long, you need to optimize the query and achieve acceptable response times.

If you suspect that an SQL originating from ORDS takes too long in the database (and not elsewhere in the tech stack), you will easily find the session in the v$session view. The answer to this question on Stack Overflow shows how it works. (The guy who answered that has many good blog posts on ORDS and REST )

More advanced ways exist, like Advanced Queuing aka Transactional Event Queues in 21c, but a simple scheduler job will do in many use cases.