Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Mar 17, 2009

Inter-process communication with DBMS_ALERT

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM