1 package dbms_alert is
2
3 ------------
4 -- OVERVIEW
5 --
6 -- This package provides support for the asynchronous (as opposed to
7 -- polling) notification of database events. By appropriate use of
8 -- this package and database triggers, an application can cause itself
9 -- to be notified whenever values of interest in the database are
10 -- changed.
11 --
12 -- For example, suppose a graphics tool is displaying a graph of some
13 -- data from a database table. The graphics tool can, after reading and
14 -- graphing the data, wait on a database alert ('dbms_alert.waitone')
15 -- covering the data just read. The tool will automatically wake up when
16 -- the data is changed by any other user. All that is required is that a
17 -- trigger be placed on the database table which then performs a signal
18 -- ('dbms_alert.signal') whenever the trigger is fired.
19 --
20 -- Alerts are transaction based. This means that the waiting session
21 -- does not get alerted until the transaction signalling the alert commits.
22 --
23 -- There can be any number of concurrent signallers of a given alert, and
24 -- there can be any number of concurrent waiters on a given alert.
25 --
26 -- A waiting application will be blocked in the database and cannot do
27 -- any other work.
28 --
29 -- Most of the calls in the package, except for 'signal', do commits.
30 --
31
32 -----------
33 -- EXAMPLE
34 --
35 -- Suppose the application wishes to graph average salaries, say by
36 -- department, for all employees. So the application needs to know
37 -- whenever 'emp' is changed. The application would look like this:
38 --
39 -- dbms_alert.register('emp_table_alert');
40 -- readagain:
41 -- <read the emp table and graph it>
42 -- dbms_alert.waitone('emp_table_alert', :message, :status);
43 -- if status = 0 then goto readagain; else <error condition>
44 --
45 -- The 'emp' table would have a trigger similar to the following:
46 --
47 -- create trigger emptrig after insert or update or delete on emp
48 -- begin
49 -- dbms_alert.signal('emp_table_alert', 'message_text');
50 -- end;
51 --
52 -- When the application is no longer interested in the alert, it does
53 -- dbms_alert.remove('emp_table_alert');
54 -- This is important since it reduces the amount of work required by
55 -- the alert signaller.
56 --
57 -- If a session exits (or dies) while there exist registered alerts,
58 -- they will eventually be cleaned up by future users of this package.
59 --
60 -- The above example guarantees that the application will always see
61 -- the latest data, although it may not see every intermediate value.
62
63
64 --------------
65 -- VARIATIONS
66 --
67 -- The application can register for multiple events and can then wait for
68 -- any of them to occur using the 'waitany' call.
69 --
70 -- An application can also supply an optional 'timeout' parameter to the
71 -- 'waitone' or 'waitany' calls. A 'timeout' of 0 returns immediately
72 -- if there is no pending alert.
73 --
74 -- The signalling session can optionally pass a message which will be
75 -- received by the waiting session.
76 --
77 -- Alerts may be signalled more often than the corresponding application
78 -- 'wait' calls. In such cases the older alerts are discaded. The
79 -- application always gets the latest alert (based on transaction commit
80 -- times).
81 --
82 -- If the application does not require transaction based alerts, then the
83 -- 'dbms_pipe' package may provide a useful alternative
84 --
85 -- If the transaction is rolled back after the call to 'dbms_alert.signal',
86 -- no alert will occur.
87 --
88 -- It is possible to receive an alert, read the data, and find that no
89 -- data has changed. This is because the data changed after the *prior*
90 -- alert, but before the data was read for that *prior* alert.
91
92
93 --------------------------
94 -- IMPLEMENTATION DETAILS
95 --
96 -- In most cases the implementation is event-driven, i.e., there are no
97 -- polling loops. There are two cases where polling loops can occur:
98 --
99 -- 1) Parallel mode. If your database is running parallel mode then
100 -- a polling loop is required to check for alerts from another
101 -- instance. The polling loop defaults to one second and is settable
102 -- by the 'set_defaults' call.
103 -- 2) Waitany call. If you use the 'waitany' call, and a signalling
104 -- session does a signal but does not commit within one second of the
105 -- signal, then a polling loop is required so that this uncommitted
106 -- alert does not camouflage other alerts. The polling loop begins
107 -- at a one second interval and exponentially backs off to 30 second
108 -- intervals.
109 --
110 -- This package uses the dbms_lock package (for synchronization between
111 -- signallers and waiters) and the dbms_pipe package (for asynchronous
112 -- event dispatching).
113
114 -------------------------------------------------------
115 -- INTERACTION WITH MULTI-THREADED AND PARALLEL SERVER
116 --
117 -- When running with the parallel server AND multi-threaded server, a
118 -- multi-threaded (dispatcher) "shared server" will be bound to a
119 -- session (and therefore not shareable) during the time a session has
120 -- any alerts "registered", OR from the time a session "signals" an
121 -- alert until the time the session commits. Therefore, applications
122 -- which register for alerts should use "dedicated servers" rather than
123 -- connecting through the dispatcher (to a "shared server") since
124 -- registration typically lasts for a long time, and applications which
125 -- cause "signals" should have relatively short transactions so as not
126 -- to tie up "shared servers" for too long.
127
128 ------------
129 -- SECURITY
130 --
131 -- Security on this package may be controlled by granting execute on
132 -- this package to just those users or roles that you trust. You may
133 -- wish to write a cover package on top of this one which restricts
134 -- the alertnames used. Execute privilege on this cover package can
135 -- then be granted rather than on this package.
136
137
138 -------------
139 -- RESOURCES
140 --
141 -- This package uses one database pipe and two locks for each alert a
142 -- session has registered.
143
144
145 ---------------------
146 -- SPECIAL CONSTANTS
147 --
148 maxwait constant integer := 86400000; -- 1000 days
149 -- The maximum time to wait for an alert (essentially forever).
150
151
152 ----------------------------
153 -- PROCEDURES AND FUNCTIONS
154 --
155 procedure set_defaults(sensitivity in number);
156 -- Set various defaults for this package.
157 -- Input parameters:
158 -- sensitivity
159 -- In case a polling loop is required (see "Implementation Details"
160 -- above), this is the time to sleep between polls. Deafult is 5 sec.
161 --
162 procedure register(name in varchar2, cleanup in boolean default TRUE);
163 -- Register interest in an alert. A session may register interest in
164 -- an unlimited number of alerts. Alerts should be de-registered when
165 -- the session no longer has any interest (see 'remove'). This call
166 -- always performs a 'commit'.
167 -- Input parameters:
168 -- name
169 -- The name of the alert in which this session is interested.
170 -- WARNING: Alert names beginning with 'ORA$' are reserved for use for
171 -- products provided by Oracle Corporation. Name must be 30 bytes
172 -- or less. The name is case-insensitive.
173 -- cleanup
174 -- This specifies whether we should perform cleanup of any orphaned
175 -- pipes that may exist and are used by the dbms_alert package. This
176 -- cleanup is only performed on the first call to "register" for each
177 -- package instantiation. The default for the parameter is TRUE.
178 --
179 procedure remove(name in varchar2);
180 -- Remove alert from registration list. Do this when the session is no
181 -- longer interested in an alert. Removing an alert is important
182 -- since it will reduce the amount of work done by signalers of the alert.
183 -- If a session dies without removing the alert, that alert will
184 -- eventually (but not immediately) be cleaned up. This call always
185 -- performs a commit.
186 -- Input parameters:
187 -- name
188 -- The name of the alert to be removed from registration list. The
189 -- name is case-insensitive.
190 --
191 procedure removeall;
192 -- Remove all alerts for this session from registration list. Do this
193 -- when the session is no longer interested in any alerts. Removing
194 -- alerts is important since it will reduce the amount of work done
195 -- by signalers of the alert. If a session dies without removing all
196 -- of its alerts, the alerts will eventually (but not immediately)
197 -- be cleaned up. This call always performs a commit.
198 --
199 -- This procedure is called automatically upon first reference to this
200 -- package during a session. Therefore no alerts from prior sessions
201 -- which may have terminated abnormally can affect this session.
202 procedure waitany(name out varchar2,
203 message out varchar2,
204 status out integer,
205 timeout in number default maxwait);
206 -- Wait for an alert to occur for any of the alerts for which this
207 -- session is registered. Although probably unusual, the same session
208 -- that waits for the alert may also first signal the alert. In this
209 -- case remember to commit after the signal and prior to the wait.
210 -- Otherwise a lock request exception (status 4) will occur. This
211 -- call always performs a commit.
212 -- Input parameters:
213 -- timeout
214 -- The maximum time to wait for an alert. If no alert occurs before
215 -- timeout seconds, then this call will return with status of 1.
216 -- Output parameters:
217 -- name
218 -- The name of the alert that occurred, in uppercase.
219 -- message
220 -- The message associated with the alert. This is the message
221 -- provided by the 'signal' call. Note that if multiple signals
222 -- on this alert occurred before the waitany call, then the message
223 -- will correspond to the most recent signal call. Messages from
224 -- prior signal calls will be discarded.
225 -- status
226 -- 0 - alert occurred
227 -- 1 - timeout occurred
228 -- Errors raised:
229 -- -20000, ORU-10024: there are no alerts registered.
230 -- Cause: You must register an alert before waiting.
231 --
232 procedure waitone(name in varchar2,
233 message out varchar2,
234 status out integer,
235 timeout in number default maxwait);
236 -- Wait for specified alert to occur. If the alert was signalled since
237 -- the register or last waitone/waitany, then this call will return
238 -- immediately. The same session that waits for the alert may also
239 -- first signal the alert. In this case remember to commit after the
240 -- signal and prior to the wait. Otherwise a lock request exception
241 -- (status 4) will occur. This call always performs a commit.
242 -- Input parameters:
243 -- name
244 -- The name of the alert to wait for. The name is case-insensitive.
245 -- timeout
246 -- The maximum time to wait for this alert. If no alert occurs before
247 -- timeout seconds, then this call will return with status of 1.
248 -- If the named alert has not been registered then the this call
249 -- will return after the timeout period expires.
250 -- Output parameters:
251 -- message
252 -- The message associated with the alert. This is the message
253 -- provided by the 'signal' call. Note that if multiple signals
254 -- on this alert occurred before the waitone call, then the message
255 -- will correspond to the most recent signal call. Messages from
256 -- prior signal calls will be discarded. The message may be up to
257 -- 1800 bytes.
258 -- status
259 -- 0 - alert occurred
260 -- 1 - timeout occurred
261 --
262 procedure signal(name in varchar2,
263 message in varchar2);
264 -- Signal an alert.
265 -- Input parameters:
266 -- name
267 -- Name of the alert to signal. The effect of the signal call only
268 -- occurs when the transaction in which it is made commits. If the
269 -- transaction rolls back, then the effect of the signal call is as
270 -- if it had never occurred. All sessions that have registered
271 -- interest in this alert will be notified. If the interested sessions
272 -- are currently waiting, they will be awakened. If the interested
273 -- sessions are not currently waiting, then they will be notified the
274 -- next time they do a wait call. Multiple sessions may concurrently
275 -- perform signals on the same alert. However the first session
276 -- will block concurrent sessions until the first session commits.
277 -- Name must be 30 bytes or less. It is case-insensitive. This call
278 -- does not perform a commit.
279 -- message
280 -- Message to associate with this alert. This will be passed to
281 -- the waiting session. The waiting session may be able to avoid
282 -- reading the database after the alert occurs by using the
283 -- information in this message. The message must be 1800 bytes or less.
284
285 end;
[Home] [Help]