Introduction

Oracle's `UTL_HTTP` package is used to make HTTP requests from within the Oracle Database. It provides functionalities to interact with web services directly using SQL and PL/SQL. However, by nature, the `UTL_HTTP.REQUEST()` function is synchronous, meaning it waits for the HTTP transaction to complete before moving on with the rest of the code. For use cases that require responsiveness or when interfacing with slow HTTP services, making these requests asynchronous can be beneficial. In this article, we’ll delve into approaches for achieving asynchronicity in `UTL_HTTP`.

Understanding Synchronous vs Asynchronous

Synchronous Requests

In a synchronous HTTP call using `UTL_HTTP`, the client (in this case, the PL/SQL procedure) must wait for the response from the server before proceeding. This can lead to inefficiencies if the server is slow to respond or if multiple requests are being made sequentially.

Asynchronous Requests

In an asynchronous processing pattern, the client sends a request to the server and continues its execution. When the response is ready, the server notifies the client or the client periodically checks for a response. This can improve system performance and user experience, especially in a database environment where resources are shared.

Making UTL_HTTP Asynchronous

Oracle's PL/SQL does not natively support asynchronous programming patterns like other programming languages (e.g., JavaScript's Promises or Python's `async`/`await`). However, you can use some workarounds to mimic asynchronous behavior:

1. Database Job Scheduler: The `DBMS_SCHEDULER` package can be utilized to run PL/SQL blocks as background jobs. This is perhaps the most straightforward approach.
2. Apex Web Service APIs: If you are using Oracle APEX, you can leverage APEX Web Service APIs, which are better suited for managing HTTP requests, especially when dealing with delays or timeouts.
3. Advanced Queuing (AQ): Oracle's Advanced Queuing allows for decoupled processing of messages. You can enqueue HTTP requests and process them asynchronously.
4. Custom Background PL/SQL Process: Implement a custom PL/SQL loop that periodically checks for tasks (e.g., HTTP requests) and executes them.

Using Database Job Scheduler

Here is an example of how to leverage the DBMS_SCHEDULER: