Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 16
Signalling Database Events with Alerters

This chapter describes how to use the `DBMS_ALERT` package to provide notification, or "alerts", of database events. Topics include the following:

- Overview
- Using Alerts
- Checking for Alerts
- Example of Using Alerts

## 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 (Romford) is active, refer to your Oracle Forms documentation.

The following procedures are callable from the `DBMS_ALERT` package:

##### Table 16-1 DBMS_ALERT Package Functions and Procedures

Function/Procedure | Description | Refer to
--- | --- | ---
    REGISTER
| Receive messages from an alert. | page 16-5
    REMOVE
| Disable notification from an alert. | page 16-5
    SIGNAL
| Signal an alert (send message to registered sessions). | page 16-6
    WAITANY
| Wait `TIMEOUT` seconds to receive alert message from an alert registered for session. | page 16-6
    WAITONE
| Wait `TIMEOUT` seconds to receive message from named alert. | page 16-7
    SET_DEFAULTS
| Set the polling interval. | page 16-8

### 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 Also: "Privileges Required" on page 10-62 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 16-2 shows the messages, and the procedures that can raise them.

##### Table 16-2 DBMS_ALERT Error Messages

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`

## 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 Also: "Database Pipes" on page 12-2.

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 16-3 . The syntax for this procedure is

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

`

##### Table 16-3 DBMS_ALERT.SIGNAL Procedure Parameters

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.

`

### 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 16-4 . The syntax for this procedure is

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

`

##### Table 16-4 DBMS_ALERT.WAITANY Procedure Parameters

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 - time-out 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.

`

### 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 16-5 . The syntax for this procedure is

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

`

##### Table 16-5 DBMS_ALERT.WAITONE Procedure Parameters

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 - time-out 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.

`

## Checking for Alerts

Usually, Oracle is event-driven; that is, 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` call.
- `WAITANY` call. If you use the `WAITANY` call, and a signalling session does a signal but does not commit within one second of the signal, then 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.

### SET_DEFAULTS Procedure

In case a polling loop is required, use the `SET_DEFAULTS` procedure to set the `POLLING_INTERVAL`. The `POLLING_INTERVAL` is the time, in seconds, to sleep between polls. The default interval is five seconds.

#### Syntax

The syntax for the `SET_DEFAULTS` procedure is

    DBMS_ALERT.SET_DEFAULTS(polling_interval  IN  NUMBER);

## Example of Using Alerts

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 the code below.

    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 the following example:

    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 the following 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, they are eventually cleaned up by future users of this package.

The above example guarantees that the application always sees the latest data, although it may not see every intermediate value.

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index