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, DBMS_LOCK
.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);
Table 2-8 WAITONE Procedure Parameters
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, 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.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|