This article originally appeared in the Oracle e-newsletter. Click here to subscribe automatically.

In many applications, it’s important to
monitor the state of a table being manipulated by another process.
If there are a lot of monitoring processes, it’s inefficient and
extremely resource intensive for each process to constantly requery
the data from the table to keep tables up to date. It would be more
efficient for the database itself to alert a process when a change
occurs.

The DBMS_ALERT package, which has been around
since Oracle8i, allows a
database-stored procedure to register itself and wait for a
database event to occur. The DBMS_ALERT package is only available
to DBA users with EXECUTE_CATALOG_ROLE privileges.

The WAITONE and WAITANY procedures will wait
until a signal is sent and committed by the other process. A
process will wait until a default time of about 1,000 days if not
specified in the call. A process must be killed by a DBA to end the
wait.

/* alert.sql */
set serveroutput on;
declare
    message varchar2(200);
    status  integer;
begin
    dbms_alert.register(‘my_alert’);
   
dbms_alert.waitone(‘my_alert’,message,status,60);
    dbms_output.put_line(‘status =
‘||status);
    dbms_output.put_line(‘message =
‘||message);
    dbms_alert.remove(‘my_alert’);
end;
/
exit;

/* alert2.sql */
exec dbms_alert.signal(‘my_alert’,’a message from another
process’);
commit;
exit;

REM – alert.cmd (Windows NT/2000)
start cmd /k sqlplus system/manager @alert
start system/manager @alert2

#!/bin/sh
# (for UNIX)
sqlplus system/manager @alert &
sqlplus system/manager @alert2

You can query information about alerts from the
SYS.DBMS_ALERT_INFO table. The NAME column is the name of the
alert, and SID is the session ID of the session that registered the
alert. CHANGED is Y if an alert has been signaled and committed.
MESSAGE is the last message passed by the signaler (multiple
signals will overwrite each other’s messages). There is one record
in this table for each registered event. For example:

SQL> exec dbms_alert.register(‘my_alert’);
/* NAME: MY_ALERT
   SID:  00070142001
   CHANGED: N
   MESSAGE:
*/

SQL> exec dbms_alert.signal(‘my_alert’,’foo’);
/* NAME: MY_ALERT
   SID:  00070142001
   CHANGED: Y
   MESSAGE: foo
*/

SQL> exec dbms_alert.signal(‘my_alert’,’foo2′);
/* NAME: MY_ALERT
   SID:  00070142001
   CHANGED: Y
   MESSAGE: foo2
*/

SQL> exec dbms_alert.remove(‘my_alert’);
no rows selected

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development. For more of his Oracle tips, visit our Oracle Dev Tips Library.