|Oracle9i Supplied PL/SQL Packages and Types Reference
Release 1 (9.0.1)
Part Number A89852-02
DBMS_ALERT, 8 of 8
This procedure waits for a specific alert to occur. An implicit
COMMIT is issued before this procedure is executed. 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,
REQUEST (which is called by
DBMS_ALERT) returns status 4.
DBMS_ALERT.WAITONE ( name IN VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout IN NUMBER DEFAULT MAXWAIT);
Name of the alert to wait for.
This is the message provided by the
SIGNALcall. If multiple signals on this alert occurred before
WAITONE, the message corresponds to the most recent
SIGNALcall. Messages from prior
SIGNALcalls are discarded.
1 - time-out occurred
If the named alert does not occurs before
timeoutseconds, this returns a status of 1.
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 */
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:
This reduces the amount of work required by the alert signaller. If a session exits (or dies) while registered alerts exist, the alerts 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.