Oracle dbms_alert Tips

About Oracle dbms_alert

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 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

Source: www.dba-oracle.com