DBMS_ALERT may not be the most elegant way to
communicate between processes but it does provide a quick and easy method.
Most of what Ive read on the Net regarding
DBMS_ALERT had to do with applications using this package to signal one another
when something had changed in the database. More specifically, assume you had a
report that needed to be generated when table data changed. If you used
old-school techniques to do this, you would schedule a cron or AT job, depending
on your operating system, that would check to see if new data was inserted,
changed, or deleted and then the scheduled job would kick off a new report. The
problem with this approach would be the extra overhead needed to query the
database every time to see if data in the tables supporting the report had
For the same scenario above, if we use
DBMS_ALERT, the processes that generate the report and the processes that
modify table data could communicate with each other and agree upon the timing
of new report generation. For instance, what if the report was data sensitive
on load patterns? A batch job could effectively modified table data and signal
to the reporting function at those data sensitive times when a new report should
be generatedputting the load process on hold until the report process signaled
back that a report was successfully generated and loading could continue.
The reporting scenario is just one where
inter-process communication with DBMS_ALERT makes sense. Below Ive created a
set of time sensitive actions that help clarify how this might work with
DBMS_ALERT communicating between two SQLPlus sessions. In the example, I wanted
to create a scenario where all logon requests to the database had to go through
an administrator console. In the table below, the Action column describes
briefly what will be done or experienced, Session #1 is considered the
administrator console session, and Session #2 is considered the user wishing to
connect to the database.
To step through this example:
1. Logon to the database as a privileged user and create
the LOGON_TRIGGER. Every user that attempts a logon will execute this trigger.
Realizing this trigger will execute for privileged users (sys, system, DBAs,
etc.) if you ever wanted to really use this, you would more than likely put in
some traps to evaluate the user and perform different actions.
a. The key to the logon trigger is the DBMS_ALERT package
that sends a SIGNAL to an administrator (procedure in next step) telling him
that the user would like to logon. This is done through the logon_request
b. The logon session then REGISTERs with an alert of
interest, the logon_wait alert.
c. The logon session then waits (WAITONE) for a message
on the logon_wait alert. Do notice that there has been a 60-second timeout
assigned to this wait. After the 60 seconds, the user will automatically
execute through the logon trigger. This was done, since it is a logon trigger,
so that access to the database would not be shutdown for everyone. Id hate to
have a timeout of 86400, along with a bug in the code, and then no one could
login for 24 hours. Modify the code cautiously if you intend to use it, I dont
want to get an email.
d. The logon session then disables (REMOVES) itself from
any notification on the alert
2. For the administrative console(s), execute the
following code to wait for logon requests. This will effectively make your
session wait for a message.
a. The session first REGISTERs with an alert of interest,
b. Next the session will then wait (WAITONE) for a
message on the logon_request alert. Again, note that I have put a 60-second
limit on this wait before it times out. Changing this timeout is not as
critical as the timeout within the LOGON_TRIGGER as you can always kill this
session if needed.
c. The DBMS_OUTPUT.PUT_LINEs will display a message on
the administrators console when someone sends a message from the LOGON_TRIGGER
d. After receiving a signal from a logon session the
administrator console will disable (REMOVE) itself from any notification on the
3. When a session tries to logon, with sqlplus in this
case, the SQLPlus logon session will SIGNAL the administrator console (through
the LOGON_TRIGGER) and then hang (60 seconds).
4. The administrator console session will receive a set
of messages asking for logon permission. Notice that my O/S user was oracle.
5. After the logon request is sent to the administrator the
SQLPlus session is hung or will be waiting for a subsequent alert notification.
The administrator SIGNALS back that the requester is good to continue.
6. Session #2 gets the SQL>; prompt
Create logon trigger
CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
SELECT SYS_CONTEXT('USERENV', 'OS_USER')
INTO v_user FROM dual;
DBMS_ALERT.SIGNAL('logon_request','Can '||v_user||' logon please?');
to wait for
wait for a
set serveroutput on;
dbms_output.put_line('status = '||v_status);
dbms_output.put_line('message = '||v_message);
status = 0
message = Can oracle logon please?
EXEC DBMS_ALERT.SIGNAL('logon_wait','Good to continue');
The example itself is
somewhat rudimentary as there is no sending or checking of statuses, no
checking of message content, and it could benefit from some looping involved
for the administrator console or a GUI front end that would subsequently
iterate through requests. Adding these extra features could build this out into
a nice security front end for various logon requests. Nevertheless, hopefully youve
gained a bit more appreciation for the DBMS_ALERT package if you havent
already. This package has been around since version 8 and I venture to say that
many havent even heard of it or played around with its potential.
Many times, we wonder how
to schedule jobs, halt, suspend, and continue given certain conditions within
the database or application runtime statuses. Making use of DBMS_ALERT has the
potential to fill those voids without the use of crontab, AT, or some other
elaborate scheduling mechanism.
See All Articles by Columnist James Koopmann