12.4 How do I…Monitor the database asynchronously with DBMS_ALERT?

Problem

I want my application to be alerted when certain events take place in the database. I do not want to continuously query an object to determine if the event has occurred; I want to be notified by the database. How do I execute an action based on an event in the database?

Technique

The DBMS_ALERT package is used to alert a session when an event occurs in the database. The receiving session registers for notifications from an alert, and the alert signals the session when the event occurs. Table 12.7 shows the procedures in the DBMS_ALERT package.

Table 12.7 The DBMS_ALERT package

Name Type Description
REGISTER Procedure Registers the session to receive messages from an alert.
WAITONE Procedure Waits for a signal from a specific alert.
WAITANY Procedure Waits for any of the alerts registered for the session.
SIGNAL Procedure Signals an alert causing messages to be sent to registered sessions.
SET_DEFAULTS Procedure Sets the polling interval.
REMOVE Procedure Removes the session from receiving notifications from an alert.
REMOVEALL Procedure Removes the session from all alert registrations.

The REGISTER procedure is used by applications to register interest in an alert. The name of the alert is passed as an IN parameter. A session may register interest in more than one alerts.

Once an alert has been registered, the WAITONE procedure can be used to wait for a signal from a specific alert or the WAITANY procedure can be used to wait for a signal from any of the alerts for which the session is registered.

The SIGNAL procedure notifies all sessions that have registered on the alert and optionally sends a message to each session. A COMMIT statement must be issued after calling the SIGNAL procedure for the call to the SIGNAL procedure to become effective.

If the transaction rolls back, the SIGNAL call has no effect. If the alerted session has blocked in a WAITONE/WAITANY call, the call returns and execution resumes with the next statement. If the alert was signaled before issuing a call to the WAITONE/WAITANY procedure, the call returns immediately. Multiple sessions can concurrently signal on the same alert. If the SIGNAL procedure was called more than once for an alert before the receiving end issued a WAITONE/WAITANY call, then the message received will be from the last SIGNAL call.

A polling loop is not required in most cases, except by the WAITANY call when a SIGNAL call remains uncommitted longer than a second, and to check for alerts from another instance when the database is running in parallel mode. The polling loop interval defaults to one second but can be changed by calling the SET_DEFAULTS procedure.

Steps

1. Run SQL*Plus and connect as the WAITE user account. This How-To requires two connections to the same database. This can be performed with two SQL*Plus sessions in a 32-bit environment, such as Windows NT or Windows 95, or can be performed using two computers connected to the same database. The first connection will register its interest in an alert and wait for the alert to fire. Run the CHP12_14.SQL file in SQL*Plus. Figure 12.15 shows the operation in SQL*Plus before the alert is fired.

Set the SERVEROUTPUT system variable to ON to display the output using the DBMS_OUTPUT.PUT_LINE procedure. The STATUS variable declared in line 2 returns the status of the WAITONE procedure call to the module. The MESSAGE variable declared in line 3 returns the message sent by the session signaling the alert. Line 5 executes the REGISTER procedure to register interest in the WAITE_ALERT alert. Line 6 executes the WAITONE procedure, making the module wait for the alert to fire.

The first IN parameter is the alert name on which the procedure is waiting. The second parameter is an OUT parameter returning the message sent when the alert is signaled. The third parameter is also an OUT parameter returning the status of the procedure call. A value of 0 identifies that an alert was fired; a value of 1 means that the timeout occurred. The last IN parameter is the timeout in seconds.

If timeout is not specified, it defaults to DBMS_ALERT.MAXWAIT, which is 1,000 days. Line 7 checks the status variable and displays the timeout message if it occurred or the alert message if fired. When the PL/SQL block is executed, it hangs until another session fires the alert.

2. Establish another SQL*Plus session to the same database and connect as the WAITE user account. This session will be used to fire alerts that are received by the first session. The SIGNAL procedure in the DBMS_ALERT package is used to fire an alert.

SQL> EXEC DBMS_ALERT.SIGNAL(‘WAITE_ALERT’, ‘THE ALERT HAS FIRED’);

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

At the SQL*Plus prompt, execute the SIGNAL procedure from the DBMS_ALERT package. The first parameter identifies the alert fired. The second parameter is optional and specifies a message sent to all processes receiving the alert. Then issue a COMMIT statement to commit the transaction. Because the DBMS_ALERT package uses tables and database triggers to provide its functionality, it requires a COMMIT to be executed before the alerts are fired.

Watch the first session after executing the statements in the second session. The first session displays the message sent by the alert. Figure 12.16 shows the message displayed in the first session.

3. Use the first session to run CHP12_15.SQL in SQL*Plus, as shown in Figure 12.17. The PL/SQL statement in the file uses the WAITANY procedure to wait for more than one alert to fire.

The ALERT_NAME variable defined in line 4 returns the name of the alert fired to the PL/SQL module. Lines 6 through 8 register three alerts that the module waits for. CANCEL_ALERT terminates the module when the alert name is checked in line 15. As soon as one of the other alerts is fired, the alert name and message are displayed by line 18. Until the CANCEL_ALERT alert is not signaled, the loop continues to block in the call to the WAITONE procedure and waits for any of the registered alerts to fire.

4. Use the second session to execute CHP12_16.SQL, watching the results in the first session. CHP12_16.SQL executes the SIGNAL procedure for firing alerts to be displayed by the first session. Figure 12.18 shows the operation performed in the second session. Figure 12.19 shows its results in the first session.

5. When the session is no longer interested in an alert, it should remove the alert using the REMOVE procedure. Unregistering an alert frees up resources used. Run the CHP12_17.SQL file in SQL*Plus, as shown in Figure 12.20, to remove the alerts registered in this How-To.

How It Works

The SIGNAL procedure of the DBMS_ALERT package is used to fire an alert on the database. Sessions that expressed an interest in the alert by executing the REGISTER function receive the alert when they execute the WAITONE or WAITANY procedures. The WAITONE procedure waits for a specific alert to fire and the WAITANY procedure waits for any of the registered alerts to fire.

This How-To requires two SQL*Plus sessions connected to the same database. Step 1 registers interest in an alert and execute the WAITONE procedure to wait for it to fire. Step 2 establishes a second connection to the database and fires the alert by executing the SIGNAL procedure.

When the alert is fired, the PL/SQL module in the first session displays the alert message and terminates. Step 3 registers three alerts and executes the WAITANY procedure within a loop to receive alerts as they are fired. When a specific alert is fired, the module is terminated. Step 4 fires alerts from the second session to display the results in the first session. Step 5 removes the alerts registered in this How-To. The SYS.DBMS_ALERT_INFO table can be queried to list information about registered alerts in the system.

Comments

The DBMS_ALERT package is useful in providing asynchronous notification of database events. The DBMS_ALERT package uses tables and triggers requiring that commits be executed before the alert is fired. This can cause problems in development environments, such as Developer/2000, which manages commits at the form level. If you do not commit the form, the alert will not fire. If your application does not require transaction based alerts, then using the DBMS_PIPE package discussed in How-To 12.3 may provide a better alternative.

Typically, the DBMS_ALERT package is used for one-way communication, whereas the DBMS_PIPE package is used for two-way communication. The DBMS_PIPE package internally uses the DBMS_LOCK and the DBMS_PIPE packages.

The privileges here include access being permitted to users with the EXECUTE ANY PROCEDURE or they are granted EXECUTE privileges on the DBMS_ALERT package.