Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Oracle dbms_alert Tips

Oracle Database Tips by Donald Burleson

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 ( 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> );

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

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:

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN


 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SENSITIVITY                    NUMBER                  IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 MESSAGE                        VARCHAR2                IN

 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                OUT
 MESSAGE                        VARCHAR2                OUT
 STATUS                         NUMBER(38)              OUT
 TIMEOUT                        NUMBER                  IN     DEFAULT

 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.




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.