Oracle7 Server Application Developer's Guide

Contents Index Home Previous Next

Using Alerts

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

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

The signalling session can optionally pass a message that will be 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 [*].

If the transaction is rolled back after the call to DBMS_ALERT.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.

REGISTER Procedure

The REGISTER procedure allows a session to register interest in an alert. The name of the alert is the IN parameter. 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.

Warning: Alert names beginning with 'ORA$' are reserved for use for products provided by Oracle Corporation.

Syntax

The syntax for the REGISTER procedure is

DBMS_ALERT.REGISTER(name  IN  VARCHAR2);

REMOVE Procedure

The REMOVE procedure allows 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.

If a session dies without removing the alert, that alert is eventually (but not immediately) cleaned up.

Syntax

The syntax for the REMOVE procedure is

DBMS_ALERT.REMOVE(name  IN  VARCHAR2);

SIGNAL Procedure

Call SIGNAL to signal an alert. The effect of the SIGNAL call only occurs when the transaction in which it is made commits. If the transaction rolls back, the SIGNAL call 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, then 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.

Syntax

The parameters for the SIGNAL procedure are described in Table 12 - 3. The syntax for this procedure is

DBMS_ALERT.SIGNAL(name     IN  VARCHAR2,
                  message  IN  VARCHAR2);

Parameter Description
name Specify the name of the alert to signal.
message Specify the 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.
Table 12 - 3. DBMS_ALERT.SIGNAL Procedure Parameters

WAITANY Procedure

Call WAITANY to wait for an alert to occur for any of the alerts for which the current session is registered. 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.

Syntax

The parameters for the WAITANY procedure are described in Table 12 - 4. The syntax for this procedure is 

DBMS_ALERT.WAITANY(name     OUT  VARCHAR2,
                   message  OUT  VARCHAR2,
                   status   OUT  INTEGER,
                   timeout   IN  NUMBER DEFAULT MAXWAIT);

Parameter Description
name Returns the name of the alert that occurred.
message Returns the message associated with the alert. This is the message provided by the SIGNAL call. Note that if multiple signals on this alert occurred before the WAITANY call, then the message corresponds to the most recent signal call. Messages from prior SIGNAL calls are discarded.
status The values returned and their associated meanings are as follows:
0 - alert occurred 1 - timeout occurred
timeout Specify the maximum time to wait for an alert. If no alert occurs before TIMEOUT seconds, this call returns with a status of 1.
Table 12 - 4. DBMS_ALERT.WAITANY Procedure Parameters

WAITONE Procedure

You call WAITONE to wait for a specific alert to occur. 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.

Syntax

The parameters for the WAITONE procedure are described in Table 12 - 5. The syntax for this procedure is

DBMS_ALERT.WAITONE(name      IN  VARCHAR2,
                   message  OUT  VARCHAR2,
                   status   OUT  INTEGER,
                   timeout   IN  NUMBER DEFAULT MAXWAIT);

Parameter Description
name Specify the name of the alert to wait for.
message Returns the message associated with the alert. This is the message provided by the SIGNAL call. Note that if multiple signals on this alert occurred before the WAITONE call, then the message corresponds to the most recent signal call. Messages from prior SIGNAL calls are discarded.
status The values returned and their associated meanings are as follows:
0 - alert occurred 1 - timeout occurred
timeout Specify the maximum time to wait for an alert. If the named alert does not occurs before TIMEOUT seconds, this call returns with a status of 1.
Table 12 - 5. DBMS_ALERT.WAITONE Procedure Parameters

Contents Index Home Previous Next