Inter-process communication with DBMS_ALERT

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 I’ve 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
changed—completely unwanted.

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 generated—putting 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 I’ve 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
alert.

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. I’d 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 don’t
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,
the logon_request.

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
(DBMS_ALERT.SIGNAL).

d.  After receiving a signal from a logon session the
administrator console will disable (REMOVE) itself from any notification on the
alert logon_request.

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


Action


Session #1


Session #2


Create logon trigger


CREATE OR REPLACE TRIGGER LOGON_TRIGGER
AFTER LOGON ON DATABASE
DECLARE
v_user varchar2(64);
v_message varchar2(200);
v_status integer;
BEGIN
SELECT SYS_CONTEXT(‘USERENV’, ‘OS_USER’)
INTO v_user FROM dual;
DBMS_ALERT.SIGNAL(‘logon_request’,’Can ‘||v_user||’ logon please?’);
DBMS_ALERT.REGISTER(‘logon_wait’);
DBMS_ALERT.WAITONE(‘logon_wait’,v_message,v_status,60);
DBMS_ALERT.REMOVE(‘logon_wait’);
END;
/


 



Execute code
to wait for
logon request

Session will
wait for a
message
request
for long


set serveroutput on;
DECLARE
v_message varchar2(200);
v_status integer;
BEGIN
DBMS_ALERT.REGISTER(‘logon_request’);
DBMS_ALERT.WAITONE(‘logon_request’,v_message,v_status,60);
dbms_output.put_line(‘status = ‘||v_status);
dbms_output.put_line(‘message = ‘||v_message);
DBMS_ALERT.REMOVE(‘logon_request’);
END;
/


 



Session
tries to
logon with
sqlplus


 


sqlplus
user/pwd



Message
request
received


status = 0
message = Can oracle logon please?


 


Allow logon


EXEC DBMS_ALERT.SIGNAL(‘logon_wait’,’Good to continue’);
commit;


 



Session gets
SQL> prompt


 


SQL>

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 you’ve
gained a bit more appreciation for the DBMS_ALERT package if you haven’t
already. This package has been around since version 8 and I venture to say that
many haven’t 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

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles