Oracle® Database PL/SQL Packages and Types Reference 10g Release 2 (10.2) Part Number B14258-02 |
|
|
PDF · Mobi · ePub |
DBMS_ALERT
supports asynchronous notification of database events (alerts). By appropriate use of this package and database triggers, an application can notify itself whenever values of interest in the database are changed.
This chapter contains the following topics:
Overview
Security Model
Constants
Restrictions
Exceptions
Operational Notes
Examples
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 performs a signal (SIGNAL
) whenever the trigger is fired.
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.
The DBMS_ALERT
package uses the constants shown in Table 13-1:
Table 13-1 DBMS_ALERT Constants
Name | Type | Value | Description |
---|---|---|---|
|
|
|
The maximum time to wait for an alert (1000 days which is essentially forever). |
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.
DBMS_ALERT
raises the application error -20000 on error conditions. Table 13-2 shows the messages and the procedures that can raise them.
The following notes relate to general and specific applications:
Alerts are transaction-based. This means that the waiting session is not alerted until the transaction signalling the alert commits. There can be any number of concurrent signalers 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.
An 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, the DBMS_PIPE
package may provide a useful alternative.
See Also:
Chapter 70, "DBMS_PIPE"If the transaction is rolled back after the call to SIGNAL
, 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.
Usually, Oracle is event-driven; this means that there are no polling loops. There are two cases where polling loops can occur:
Shared mode. If your database is running in shared mode, a polling loop is required to check for alerts from another instance. The polling loop defaults to one second and can be set by the SET_DEFAULTS
procedure.
WAITANY
procedure. If you use the WAITANY
procedure, and if a signalling session does a signal but does not commit within one second of the signal, a polling loop is required so that this uncommitted alert does not camouflage other alerts. The polling loop begins at a one second interval and exponentially backs off to 30-second intervals.
Table 13-2 DBMS_ALERT Error Messages
Error Message | Procedure |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Suppose you want to graph average salaries by department, for all employees. Your application needs to know whenever EMP
is changed. Your application would look similar to this code:
DBMS_ALERT.REGISTER('emp_table_alert'); <<readagain>>: /* ... read the emp table and graph it */ DBMS_ALERT.WAITONE('emp_table_alert', :message, :status); if status = 0 then goto <<readagain>>; else /* ... error condition */
The EMP
table would have a trigger similar to this:
CREATE TRIGGER emptrig AFTER INSERT OR UPDATE OR DELETE ON emp BEGIN DBMS_ALERT.SIGNAL('emp_table_alert', 'message_text'); END;
When the application is no longer interested in the alert, it makes this request:
DBMS_ALERT.REMOVE('emp_table_alert');
This reduces the amount of work required by the alert signaller. If a session exits (or dies) while registered alerts exist, the alerts are eventually cleaned up by future users of this package.
The example guarantees that the application always sees the latest data, although it may not see every intermediate value.
Table 13-3 DBMS_ALERT Package Subprograms
Subprogram | Description |
---|---|
Receives messages from an alert |
|
Disables notification from an alert |
|
Removes all alerts for this session from the registration list |
|
Sets the polling interval |
|
Signals an alert (send message to registered sessions) |
|
Waits |
|
Waits |
This procedure lets a session register interest in an alert.
DBMS_ALERT.REGISTER ( name IN VARCHAR2);
Table 13-4 REGISTER Procedure Parameters
Parameter | Description |
---|---|
|
Name of the alert in which this session is interested. |
Caution:
Alert names beginning with 'ORA$' are reserved for use for products provided by Oracle. Names must be 30 bytes or less. The name is case insensitive.
A session can register interest in an unlimited number of alerts. Alerts should be deregistered when the session no longer has any interest, by calling REMOVE
.
This procedure enables a session that is no longer interested in an alert to remove that alert from its registration list. Removing an alert reduces the amount of work done by signalers of the alert.
DBMS_ALERT.REMOVE ( name IN VARCHAR2);
Table 13-5 REMOVE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the alert (case-insensitive) to be removed from registration list. |
Removing alerts is important because it reduces the amount of work done by signalers of the alert. If a session dies without removing the alert, that alert is eventually (but not immediately) cleaned up.
This procedure removes all alerts for this session from the registration list. You should do this when the session is no longer interested in any alerts.
This procedure is called automatically upon first reference to this package during a session. Therefore, no alerts from prior sessions which may have terminated abnormally can affect this session.
This procedure always performs a commit.
DBMS_ALERT.REMOVEALL;
In case a polling loop is required, use the SET_DEFAULTS
procedure to set the polling interval.
DBMS_ALERT.SET_DEFAULTS ( sensitivity IN NUMBER);
Table 13-6 SET_DEFAULTS Procedure Parameters
Parameter | Description |
---|---|
|
Polling interval, in seconds, to sleep between polls. The default interval is five seconds. |
This procedure signals an alert. The effect of the SIGNAL
call only occurs when the transaction in which it is made commits. If the transaction rolls back, SIGNAL
has no effect.
All sessions that have registered interest in this alert are notified. If the interested sessions are currently waiting, they are awakened. If the interested sessions are not currently waiting, they are notified the next time they do a wait call.
Multiple sessions can concurrently perform signals on the same alert. Each session, as it signals the alert, blocks all other concurrent sessions until it commits. This has the effect of serializing the transactions.
DBMS_ALERT.SIGNAL ( name IN VARCHAR2, message IN VARCHAR2);
Table 13-7 SIGNAL Procedure Parameters
Parameter | Description |
---|---|
|
Name of the alert to signal. |
|
Message, of 1800 bytes or less, to associate with this alert. This message is passed to the waiting session. The waiting session might be able to avoid reading the database after the alert occurs by using the information in the message. |
Call this procedure to wait for an alert to occur for any of the alerts for which the current session is registered.
DBMS_ALERT.WAITANY ( name OUT VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout IN NUMBER DEFAULT MAXWAIT);
Table 13-8 WAITANY Procedure Parameters
Parameter | Description |
---|---|
|
Returns the name of the alert that occurred. |
|
Returns the message associated with the alert. This is the message provided by the |
|
Values returned: 0 - alert occurred 1 - timeout occurred |
|
Maximum time to wait for an alert. If no alert occurs before |
An implicit COMMIT
is issued before this procedure is executed. The same session that waits for the alert may also first signal the alert. In this case remember to commit after the signal and before the wait; otherwise, DBMS_LOCK
.REQUEST
(which is called by DBMS_ALERT
) returns status 4.
-20000, ORU-10024: there are no alerts registered.
This procedure waits for a specific alert to occur. An implicit COMMIT
is issued before this procedure is executed. A session that is the first to signal an alert can also wait for the alert in a subsequent transaction. In this case, remember to commit after the signal and before the wait; otherwise, DBMS_LOCK
.REQUEST
(which is called by DBMS_ALERT
) returns status 4.
DBMS_ALERT.WAITONE ( name IN VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout IN NUMBER DEFAULT MAXWAIT);
Table 13-9 WAITONE Procedure Parameters
Parameter | Description |
---|---|
|
Name of the alert to wait for. |
|
Returns the message associated with the alert. This is the message provided by the |
|
Values returned: 0 - alert occurred 1 - timeout occurred |
|
Maximum time to wait for an alert. If the named alert does not occurs before |