Oracle8i Supplied PL/SQL Packages Reference
Release 2 (8.1.6)

Part Number A76936-01





Go to previous page Go to next page


The DBMS_ALERT package provides support for the asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can cause itself to be notified whenever values of interest in the database are changed.

For example, suppose a graphics tool is displaying a graph of some data from a database table. The graphics tool can, after reading and graphing the data, wait on a database alert (WAITONE) covering the data just read. The tool automatically wakes up when the data is changed by any other user. All that is required is that a trigger be placed on the database table, which then performs a signal (SIGNAL) whenever the trigger is fired.

Alerts are transaction-based. This means that the waiting session does not get alerted until the transaction signalling the alert commits.There can be any number of concurrent signallers of a given alert, and there can be any number of concurrent waiters on a given alert.

A waiting application is blocked in the database and cannot do any other work.


Because database alerters issue commits, they cannot be used with Oracle Forms. For more information on restrictions on calling stored procedures while Oracle Forms is active, refer to your Oracle Forms documentation. 


Security on this package can be controlled by granting EXECUTE on this package to selected users or roles. You might want to write a cover package on top of this one that restricts the alert names used. EXECUTE privilege on this cover package can then be granted rather than on this package.


maxwait constant integer :=  86400000; -- 1000 days 

The maximum time to wait for an alert (this is essentially forever).


DBMS_ALERT raises the application error -20000 on error conditions. This table shows the messages and the procedures that can raise them.

Table 2-1 DBMS_ALERT Error Messages
Error Message  Procedure 
ORU-10001 lock request error, status: N
ORU-10015 error: N waiting for pipe status
ORU-10016 error: N sending on pipe 'X'
ORU-10017 error: N receiving on pipe 'X'
ORU-10019 error: N on lock request
ORU-10020 error: N on lock request
ORU-10021 lock request error; status: N
ORU-10022 lock request error, status: N
ORU-10023 lock request error; status N
ORU-10024 there are no alerts registered
ORU-10025 lock request error; status N
ORU-10037 attempting to wait on uncommitted signal from same 

Using Alerts

The application can register for multiple events and can then wait for any of them to occur using the WAITANY procedure.

An application can also supply an optional timeout parameter to the WAITONE or WAITANY procedures. A timeout of 0 returns immediately if there is no pending alert.

The signalling session can optionally pass a message that is received by the waiting session.

Alerts can be signalled more often than the corresponding application wait calls. In such cases, the older alerts are discarded. The application always gets the latest alert (based on transaction commit times).

If the application does not require transaction-based alerts, then the DBMS_PIPE package may provide a useful alternative.

See Also:

Chapter 31, "DBMS_PIPE" 

If the transaction is rolled back after the call to SIGNAL, then no alert occurs.

It is possible to receive an alert, read the data, and find that no data has changed. This is because the data changed after the prior alert, but before the data was read for that prior alert.

Checking for Alerts

Usually, Oracle is event-driven; this means that there are no polling loops. There are two cases where polling loops can occur:

Go to previous page Go to next page
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.