Using DBMS_ALERT for signaling events between sessions

This post is about using the alert-functionality of Oracle PL/SQL provided through the DBMS_ALERT package. It has been around for a long time, at least since Oracle 8i, but I had never used it until recently. Because it is so simple to implement I would like to show it to you.

My use case was the following: at a customer site, an application that has been around for a long time contained a custom job handler based upon DBMS_JOB. The job handler consisted of a procedure that acted as a job supervisor. This supervisor-procedure polled a custom job-table every minute, to see if there were jobs to start at that time. Once this was done, and the jobs were started (if applicable), the procedure would sleep for the remaining number of seconds until the next minute, when it would do the next poll. In essence, the supervisor was an infinite loop, that could be stopped by raising a flag in a table (setting a column value).

So, when you wanted the jobs to stop, the flag would have to be set, and in the worst case you would have to wait 59 secs until the next whole-minute poll. So YOU would perform a sleep and the job would perform a sleep….

Enter DBMS_ALERT.

The mechanism of DBMS_ALERT is that a session can signal an alert to one or more other sessions. A session will receive an alert only after it has expressed its interest in this alert. This is a prerequisite that can be met by issuing:

DBMS_ALERT.REGISTER(‘MY_ALERT’, FALSE);

The second argument here is the cleanup-indicator, new since Oracle 11.2 (11g Release 2). I use FALSE because there seem to be some issues with using true, according to this blog posting. The default for the cleanup-parameter is TRUE.

So now our session is ready to start listening for MY_ALERT. By the way, the alert name is case-insensitive. Now, if we want to listen for the alert to occur, we have to issue:

DBMS_ALERT.WAITONE(‘MYALERT’, lv_message, lv_status, 20);

Please note that the WAITONE-call will issue an implicit commit. Now, our second session is waiting for the alert (for max 20 seconds, the timeout).
lv_message will contain the optional message that was sent while issuing the alert.
lv_status will indicate what happened: 0 = the alert was received, 1 = timeout reached without receiving an alert.

Now over to our second session, the one that will send the alert. To send a signal, the SIGNAL-procedure is used. Because DBMS_ALERT uses the Oracle transaction-mechanism, a commit has to be issued to actually send the signal:

DBMS_ALERT.SIGNAL(‘MY_ALERT’, ‘This message is optional’);

COMMIT;

So, a signal has been sent now, and all sessions that registered for ‘MY_ALERT’ will receive this, if they are listening for it.

I used this mechanism to replace the DBMS_LOCK.SLEEP procedure that waited x seconds before checking a table for a stop-the-jobhandler-flag. By using the alert, I have shortened the time it takes to stop the job handler, since it will stop immediately instead of after the sleep-period. The DBMS_ALERT.WAITONE call still function as a SLEEP if no alert is received, the timeout of WAITONE is the same as the original SLEEP period.

Lastly, we have to do the housekeeping. In the first session, the code that listened for the alert has to undo the REGISTER-call when the procedure ends:

DBMS_ALERT.REMOVE;

This removes the alert from the registrations-list of this session, making life easier for future signalling sessions. There’s also a DBMS_ALERT.REMOVEALL procedure, I’m sure you can guess what it does…

Some notes on DBMS_ALERT:

– Multiple sessions can register for the same alert. If the alert is issued once, all sessions will receive it.
– A session can register for multiple alerts. DBMS_ALERT.WAITANY can be used to listen for any of the registered alerts to occur.
– An alternative to DBMS_ALERT is DBMS_PIPE, which is not transaction-based (so no COMMIT needed). However, I chose DBMS_ALERT since it seemed easier to implement than DBMS_PIPE. 

As a final note on my use case:  I know that having such a custom-made jobhandler is out of date and should be removed in favour of DBMS_SCHEDULER, but that would mean a serious change to the code and the whole mechanism, and that’s not always feasible…

The Oracle 11.2 docs of DBMS_ALERT. Please pay special attention to the operational notes mentioned on this page before you implement DBMS_ALERT.

http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_alert.htm