DBMS_ALERT Package

you can use alerts to notify you about an event for informational
purposes. The DBMS_ALERT package is typically a one-way asynchronous communication that is triggered when a transaction commits. Unless a transaction commits, no information is sent to the alert. This means that a waiting procedure or application remains idle until the desired transaction commits. Because alerts provide one-way communication, they have limited usage.

Setting up ALERT using DBMS_ALERT

Use the following Order to setting  up an alert

REGISTER-to record your interest in a particular alert.

WAITTONE-wait for a specific alert.

WAITANY-wait for any of  your registred alerts

SIGNAL-use this when the condition for the alert is met and the transaction has been committed.

Using SIGNAL to Issue an Alert

The Syntax for the SIGNAL Procedure

PROCEDURE SIGNAL(alert_name IN VARCHAR2,message_sent IN VARCHAR2);

alert_name can be a maximum of 30 characters, and it is not case sensitive. “message_sent” can be up to 1,800 characters, which allows for a generous concatenation of text, variable names, and so on. This message is sent to the waiting session.It is common for multiple sessions to concurrently issue signals on the same alert. In this case, as each session issues the alert, it blocks all other concurrent sessions until it commits.The net effect of this behavior is that alerts can cause transactions to become serialized.

Registering for an Alert

Before you can even search for an alert, you must register the alert you want to monitor,which adds you to the master registration list. You take this first step by using the REGISTER procedure.

Syntax for using REGISTER procedure

PROCEDURE REGISTER(alert_name IN VARCHAR2);

alert_name is the name of the alert to monitor.

Waiting for a Specific Alert

If you want to monitor one alert, use the WAITONE Procedure
The Syntax for the WAITONE Procedure

PROCEDURE WAITONE(alert_name IN VARCHAR2,alert_message OUT VARCHAR2,alert_status OUT INTEGER,timeout IN NUMBER DEFAULT maxwait);

Waiting for Any Registered Alert

WAITANY procedure allows you to constantly monitor for any alert for which you have registered in the current session.

The Syntax for the WAITANY Procedure

PROCEDURE WAITANY(alert_name OUT VARCHAR2, alert_message OUT VARCHAR2, alert_status OUT INTEGER,timeout IN NUMBER DEFAULT maxwait);

Removing One Alert

To remove only one specific alert from the registration list, use the REMOVE procedure.

The Syntax for the REMOVE Procedure

PROCEDURE REMOVE(alert_name IN VARCHAR2);

Removing All Alerts

You can remove all registered alerts from the current session by placing a call to the procedure REMOVEALL procedure is as follows:

PROCEDURE REMOVEALL;

After the procedure is executed, all registered alerts are deleted. An implicit COMMIT is executed with this call.

Written by admin on January 19th, 2011 with comments disabled.
Read more articles on Oracle Packages.

Comments disabled

Comments on this article have been disabled.