DBMS_ALERT, 2 of 2

Summary of Subprograms

Table 2-2 DBMS_ALERT Package Subprograms

Subprogram	Description
REGISTER Procedure	Receives messages from an alert.
REMOVE Procedure	Disables notification from an alert.
REMOVEALL Procedure	Removes all alerts for this session from the registration list.
SET_DEFAULTS Procedure	Sets the polling interval.
SIGNAL Procedure	Signals an alert (send message to registered sessions).
WAITANY Procedure	Waits `timeout` seconds to receive alert message from an alert registered for session.
WAITONE Procedure	Waits `timeout` seconds to receive message from named alert.

REGISTER Procedure

This procedure lets a session 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.

Syntax

DBMS_ALERT.REGISTER (
   name  IN  VARCHAR2);

Parameters

Table 2-3 REGISTER Procedure Parameters

Parameter	Description
name	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 Corporation. Names must be 30 bytes or less. The name is case-insensitive.

REMOVE Procedure

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.

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.

Syntax

DBMS_ALERT.REMOVE (
   name  IN  VARCHAR2);

Parameters

Table 2-4 REMOVE Procedure Parameters

Parameter	Description
name	Name of the alert (case-insensitive) to be removed from registration list.

REMOVEALL Procedure

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.

Syntax

DBMS_ALERT.REMOVEALL;

Parameters

None.

SET_DEFAULTS Procedure

In case a polling loop is required, use the SET_DEFAULTS procedure to set the polling interval.

Syntax

DBMS_ALERT.SET_DEFAULTS (
   polling_interval  IN  NUMBER);

Parameters

Table 2-5 SET_DEFAULTS Procedure Parameters

Parameter	Description
polling_interval	Time, in seconds, to sleep between polls. The default interval is five seconds.

Parameter

Description

polling_interval

Time, in seconds, to sleep between polls.

The default interval is five seconds.

SIGNAL Procedure

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, then SIGNAL has no effect.

All sessions that have registered interest in this alert are notified. If the interested sessions are currently waiting, then 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

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

Parameters

Table 2-6 SIGNAL Procedure Parameters

Parameter	Description
name	Name of the alert to signal.
message	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.

Parameter

Description

name

Name of the alert to signal.

message

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

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

Parameters



Table 2-7  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. If multiple signals on this alert occurred before WAITANY, then the message corresponds to the most recent SIGNAL call. Messages from prior SIGNAL calls are discarded. 

    
status
     



Values returned:


0 - alert occurred


1 - time-out occurred 

    
timeout
     



Maximum time to wait for an alert.


If no alert occurs before timeout seconds, then this returns a status of 1.

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. If multiple signals on this alert occurred before `WAITANY`, then the message corresponds to the most recent `SIGNAL` call. Messages from prior `SIGNAL` calls are discarded.
status	Values returned: 0 - alert occurred 1 - time-out occurred
timeout	Maximum time to wait for an alert. If no alert occurs before `timeout` seconds, then this returns a status of 1.

Errors

-20000, ORU-10024: there are no alerts registered.

Cause:

You must register an alert before waiting.

WAITONE Procedure

This procedure waits 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

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

Parameters



Table 2-8   WAITONE Procedure Parameters 
 





Parameter 


Description 


    
name
     



Name of the alert to wait for. 

    
message
     



Returns the message associated with the alert.


This is the message provided by the SIGNAL call. If multiple signals on this alert occurred before WAITONE, then the message corresponds to the most recent SIGNAL call. Messages from prior SIGNAL calls are discarded. 

    
status
     



Values returned:


0 - alert occurred


1 - time-out occurred 

    
timeout
     



Maximum time to wait for an alert.


If the named alert does not occurs before timeout seconds, this returns a status of 1.

Parameter	Description
name	Name of the alert to wait for.
message	Returns the message associated with the alert. This is the message provided by the `SIGNAL` call. If multiple signals on this alert occurred before `WAITONE`, then the message corresponds to the most recent `SIGNAL` call. Messages from prior `SIGNAL` calls are discarded.
status	Values returned: 0 - alert occurred 1 - time-out occurred
timeout	Maximum time to wait for an alert. If the named alert does not occurs before `timeout` seconds, this returns a status of 1.

Example

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, then 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.