Using DBMS_ALERT to Signal Job's Completion
The DBMS_ALERT package supports asynchronous notification of database events (alerts). It provides APIs to send alerts, register for alerts, and wait to receive them. The procedures this Solution uses are SIGNAL, REGISTER, and WAITFORANY:
DBMS_ALERT.SIGNAL procedure signals an alert. The SIGNAL call goes into effect only when the transaction in which it is made commits. If the transaction rolls back, SIGNAL has no effect. All sessions that have registered interest in this alert are notified. (If the application does not require transaction-based alerts, the DBMS_PIPE package may provide a useful alternative.)
DBMS_ALERT.REGISTER procedure registers interest in an alert.
DBMS_ALERT.WAITFORANY procedure waits for any of the alerts for which the current session is registered to occur. (For more details see Oracle9i Supplied PL/SQL Packages and Types Reference, Release 2 (9.2), DBMA_ALERT package. Access requires an Oracle.com login.)
As you see, the DBMS_ALERT package has exactly what you need. The main session carries out the following procedure:
- Runs N jobs in parallel
- Registers for N alerts (one per job)
- Submits the jobs to a job queue
- Waits to be notified by all of the alerts
I created a JobsPkg package that provides this functionality. Listing 1 shows the package specification. Listing 2provides the package body.
Take a closer look at the code in the WaitForCompletion procedure. It takes the following steps:
- Builds an associative array of N jobs indexed by string (new in Oracle 9i)
- Registers for all N alerts
- Waits for alerts (As soon as it gets notified, it removes the alert from the associative array built in step 1 and gets back in waiting mode. When the array gets empty, the procedure has received all of the alerts you registered for, all of the jobs are finished, and it's time to exit the loop.)
Author warning: Oracle restricts alert names to 30 characters each and converts them to upper case internally! Therefore, if a session signals a
DemoPkg.Sleep alert, the sessions interested in that alert have to register for the
DEMOPKG.SLEEP alert. Otherwise, these sessions would remain in waiting mode forever. That "small" detail may cause some frustration, especially in debugging processes that run in the background. Trust me, I learned the hard way. The code in the private BuildAlertName function resolves these case sensitivity and length limitation issues.