Data Management

Oracle Tip: Generate database alerts with the DBMS_ALERT package

Learn how to create alerts with the DBMS_ALERT package, which allows a database-stored procedure to register itself and wait for a database event to occur.

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.

Editor's Picks

Free Newsletters, In your Inbox