DBMS_ALERT

DBMS_ALERT supports asynchronous notification of database events (alerts).

DBMS_ALERT.REGISTER (name IN VARCHAR2);  -- Name of the alert & it is case insensitive

This procedure lets a session register interest in an alert.


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

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.


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

Call this procedure to wait for an alert to occur for any of the alerts for which the current session is registered.
An implicit COMMIT is issued before this procedure is executed.

status:
0 - alert occurred
1 - timeout occurred

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


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

This procedure waits for a specific alert to occur. An implicit COMMIT is issued before this procedure is executed.


DBMS_ALERT.SET_DEFAULTS (sensitivity  IN  NUMBER); -- sensitivity,in seconds, to sleep between polls. The default interval is five seconds


DBMS_ALERT.REMOVE (name  IN  VARCHAR2);
This procedure enables a session that is no longer interested in an alert to remove that alert from its registration list.

DBMS_ALERT.REMOVEALL;
This procedure removes all alerts for this session from the registration list.


Example:

Session 1:


DECLARE
   status       NUMBER;
   MESSAGE      VARCHAR2 (200);
   signalname   VARCHAR2 (30);
BEGIN
   DBMS_ALERT.REGISTER ('testsignal1');
   DBMS_ALERT.REGISTER ('testsignal0');
   DBMS_OUTPUT.put_line ('waiting for a single');
   DBMS_ALERT.WAITANY (signalname,
                                              MESSAGE,
                                              status,
                                              1);
   DBMS_OUTPUT.put_line (signalname);
   DBMS_OUTPUT.put_line (status);
   DBMS_OUTPUT.put_line (MESSAGE);
END;



Session 2:


begin
DBMS_ALERT.Signal('testsignal1','Job 1 done');
commit;
end;


Session 3:


begin
DBMS_ALERT.Signal('testsignal0','Job 0 done');
commit;
end;




Session 1 registered for two alerts. Session 2 and Session 3 signal alert completion.