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.
