DBMS_ALERT: Broadcasting Alerts to Users

The DBMS_ALERT package provides a facility to broadcast notification of database events (alerts) to multiple users who have previously registered their interest in receiving those alerts. You will use the DBMS_ALERT package to implement applications that respond immediately to data modifications of interest to the application. In this way, you can avoid the need to do regular polling on the data to determine if changes have taken place. This is typically accomplished by having the application register to receive an alert on the specific data of interest, querying the data to establish a baseline, and then waiting for the alert to be signaled, which indicates the need to requery the data. Alerts can be automatically signaled using database triggers on the tables of interest, so that all modifications to the data will signal the alert, regardless of which application or user modified the data. Alerts are asynchronous and transaction-based, meaning that users can wait for and receive notification after the signaling event and that only committed transactions (usually involving data changes) will signal the alert.

Here are two good examples of applications that could be implemented using DBMS_ALERT:

  • Graphical displays of statistics that must be updated whenever the underlying data changes

  • An online auction where bidders want to be notified when they have been outbid on an item

Getting Started with DBMS_ALERT

The DBMS_ALERT package is created when ...

Get Oracle Built-in Packages now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.