|
About Oracle
dbms_alert
The dbms_alert package begins with a client process registering interest in
an alert. Once it registers for an alert, it waits for that specific alert (waitone)
or any other alert (waitany) to occur. Once the alert occurs, the client is
notified and a string is passed containing whatever the signal process
selected to send to the client (Figure 5.1).
Once the call to waitone or waitany is made, Oracle will check the
dbms_alert_info table to see if any new rows have been inserted. If a new
row exists, the data in the column message is returned from the procedure
call.
The Java program below (Alert.java) can be executed from the DOS or UNIX
prompt. This program prompts for database connection information, connects
to the database via JDBC, registers for an alert, and waits for the
LOGONALERT alert to occur. It can easily be customized to not only display
an event but to prompt for the event of interest.
Next, a trigger will be created that will signal this event. The signal code
is the key to the trigger below. Notice that it contains the same alert name
that the client registered, LOGONALERT.
dbms_alert.signal('LOGONALERT', ,<any string we want to send> );
commit;
The following PL/SQL (Logontrigger.sql) is an AFTER LOGON TRIGGER that sends
information to those registered for LOGONALERT. As part of the signal call,
it returns the user, machine, and time that the user logged on. The commit
statement after the signal call is required to send the signal.
Once everything is configured, the trigger will fire when a user connects to
the database. From the operating system, the program will be started and the
required information entered.
C:\oracle9i\bin>java Alert
************************************************
* This is a simple utility to test *
* the alert notification between the database *
* and any client application *
************************************************
Enter Host Name: MoorePC
Enter Database Name: ORCL92
Enter TNS Listener Port#: 1521
Enter Database User Name: scott
Enter Database User Password: tiger
Press <Ctrl><C> at any time to exit
Successully connected to ORCL92 as scott
User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:18:37
SCOTT was the user that connected to the database to wait for the event.
Within one second of user SCOTT logging in to the database, the LOGONALERT
was signaled and sent to the Java client. Based on the message above, the
SYS user logged in to the database.
Duplicating this Java code functionality in SQL*Plus would be simple,
although not as functional, since SQL*Plus is not automatically notified of
the event. The print statement below is used to check the status of the
logon alert message.
SQL> var out1 varchar2(800)
SQL> var out2 varchar2(800)
SQL> exec dbms_alert.waitone('LOGONALERT', :out1, :out2);
PL/SQL procedure successfully completed.
SQL> print out1
OUT1
-------------------------------------------------------------------
User: SYS logged on from WORKGROUP\MOOREPC at 01-02-2003 21:24:55
The
dbms_alert package is
created by executing the
catproc.sql file and is
owned by SYS. Once granted the
execute privilege to
dbms_alert, it can be
executed by any software
component that can call a stored
procedure including SQL*Plus,
Java and Pro*C.
The
dbm_alert procedure is
rarely used.
The
dbms_alert package provides
a mechanism for the database to
notify a client (anything
listening) of an event
asynchronously, which means that
the application does not need to
periodically check for the
occurrence of events.
With
dbms_alert, when an event
occurs, a notification will be
sent. Prior to
dbms_alert, developers created a
polling process that checked the
status of something on the
database, like a completed job,
by checking for a table value
that the process had just
updated. dbms_alert
renders such techniques obsolete
and is one of the best Oracle
supplied packages.
The
dbms_alert package is even
more helpful when dealing with 3
tier web applications - client,
web server, and database. Web
applications are "stateless" by
nature, meaning that the web
server processes a request and
it's done - there is no tethered
connection like we're accustomed
to with SQL*Plus, Oracle
Applications, or SAP R/3. The
Oracle dbms_alert
provides a way for the database
to initiate contact with the web
server, who in turn can notify
clients attached to it.
Description of the
DBMS_ALERT package:
PROCEDURE DBMS_ALERT.REGISTER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PROCEDURE DBMS_ALERT.REMOVE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
PROCEDURE DBMS_ALERT.REMOVEALL
PROCEDURE DBMS_ALERT.SET_DEFAULTS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SENSITIVITY NUMBER IN
PROCEDURE DBMS_ALERT.SIGNAL
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
MESSAGE VARCHAR2 IN
PROCEDURE DBMS_ALERT.WAITANY
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 OUT
MESSAGE VARCHAR2 OUT
STATUS NUMBER(38) OUT
TIMEOUT NUMBER IN DEFAULT
PROCEDURE DBMS_ALERT.WAITONE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
NAME VARCHAR2 IN
MESSAGE VARCHAR2 OUT
STATUS NUMBER(38) OUT
TIMEOUT NUMBER IN DEFAULT
|
|
 |
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|
