Oracle7 Server Application Developer's Guide
Overview
The DBMS_ALERT package provides support for the asynchronous (as opposed to polling) notification of database events. 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 (DBMS_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 (DBMS_ALERT.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.
Note: Because database alerters issue COMMITS, they cannot be used with Oracle Forms. For more information on restrictions on calling stored procedures while Oracle Forms (Runform) is active, refer to your Oracle Forms documentation.
The following procedures are callable from the DBMS_ALERT package:
| Function/Procedure
| Description
| Refer to
|
| REGISTER
| Receive messages from an alert.
| ![[*]](jump.gif)
|
| REMOVE
| Disable notification from an alert.
|
|
| SIGNAL
| Signal an alert (send message to registered sessions).
|
|
| WAITANY
| Wait TIMEOUT seconds to receive alert message from an alert registered for session.
|
|
| WAITONE
| Wait TIMEOUT seconds to receive message from named alert.
|
|
| SET_DEFAULTS
| Set the polling interval.
|
|
Table 12 - 1. DBMS_ALERT Package Functions and Procedures
Creating the DBMS_ALERT Package
To create the DBMS_ALERT package, submit the DBMSALRT.SQL and PRVTALRT.PLB scripts when connected as the user SYS. These scripts are run automatically by the CATPROC.SQL script. See for information on granting the necessary privileges to users who will be executing this package.
Security
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.
Errors
DBMS_ALERT raises the application error -20000 on error conditions. Table 12 - 2 shows the messages, and the procedures that can raise them.
| Error Message
| Procedure
|
| ORU-10001 lock request error, status: N
| SIGNAL
|
| ORU-10015 error: N waiting for pipe status
| WAITANY
|
| ORU-10016 error: N sending on pipe 'X'
| SIGNAL
|
| ORU-10017 error: N receiving on pipe 'X'
| SIGNAL
|
| ORU-10019 error: N on lock request
| WAIT
|
| ORU-10020 error: N on lock request
| WAITANY
|
| ORU-10021 lock request error; status: N
| REGISTER
|
| ORU-10022 lock request error, status: N
| SIGNAL
|
| ORU-10023 lock request error; status N
| WAITONE
|
| ORU-10024 there are no alerts registered
| WAITANY
|
| ORU-10025 lock request error; status N
| REGISTER
|
| ORU-10037 attempting to wait on uncommitted signal from same session
| WAITONE
|
Table 12 - 2. DBMS_ALERT Error Messages