Oracle DBMS_ALERT

Oracle DBMS_ALERT is a built-in package provided by Oracle Database that allows communication and notification between database sessions. It enables one session to send alerts or notifications to other sessions within the same database instance. This feature is particularly useful in scenarios where real-time communication or coordination is required between different database sessions or applications.

DBMS_ALERT works based on a publish-subscribe model, where a session can publish an alert message and other subscribing sessions can receive and respond to those alerts. The package provides procedures and functions that allow sessions to perform various operations related to alerts.

Concepts

Here are some key components and concepts associated with Oracle DBMS_ALERT package:

Alert: An alert is a message that can be published by one session and received by other subscribing sessions. It consists of a name and a message payload.

Channel: A channel represents a communication pathway through which alerts are transmitted. Each alert is associated with a specific channel.

Registration: Sessions can register to receive alerts on a specific channel. Once registered, a session becomes a subscriber and can receive alerts published on that channel.

Notification: When an alert is published on a channel, Oracle Database notifies all the subscribed sessions by signaling an asynchronous event. Subscribed sessions can then retrieve the alert message.

Payload: The payload of an alert is the actual message content that is published and received by sessions. It can be any string or binary data.

Timeout: DBMS_ALERT supports a timeout mechanism for waiting on alerts. Subscribed sessions can specify a timeout duration, and if no alerts are received within that timeframe, they can proceed with other tasks.

Steps

Using DBMS_ALERT package involves the following steps:

Creating a Channel: First, a channel needs to be created using the DBMS_ALERT package. The channel acts as a unique identifier for the alerts published and received.

Publishing Alerts: A session can publish an alert on a specific channel using the SIGNAL procedure provided by DBMS_ALERT. The alert message and channel name are specified as input parameters.

Subscribing to Alerts: Sessions interested in receiving alerts need to register themselves as subscribers for a particular channel using the REGISTER procedure. They can also specify the timeout duration for waiting on alerts.

Receiving Alerts: Once subscribed, sessions can use the WAITANY or WAITONE procedures to wait for alerts on the registered channels. The WAITANY procedure waits for any alert on any registered channel, while WAITONE waits for an alert on a specific channel.

Handling Alerts: When an alert is received, the subscribing session can retrieve the message payload and perform any required processing or actions based on the content of the alert.

DBMS_ALERT.REGISTER example

CREATE OR REPLACE PROCEDURE 
register (p_register in varchar2) 
IS
BEGIN
DBMS_ALERT.REGISTER('alert_name'); 
END;

DBMS_ALERT.REMOVE example

CREATE OR REPLACE PROCEDURE 
remove (p_remove in varchar2) 
IS
BEGIN
DBMS_ALERT.REMOVE('alert_name'); 
END;

DBMS_ALERT.SIGNAL example

CREATE OR REPLACE PROCEDURE 
signal (
	p_name in varchar2,
	p_message in varchar2
IS
BEGIN
	DBMS_ALERT.SIGNAL('Alert_name', 'The message of alert'); 
END;

DBMS_ALERT.WAITONE example

Declare
	v_status varchar2(200);
	v_message    varchar2(1200);
Begin
dbms_alert.waitone( 
	name    => 'AlertName',
	message => v_message,
	status  => v_status,
	timeout => dbms_alert.maxwait 
);
If v_status = 'NO' Then
	dbms_output.put_line( 'The message is ' || v_message );
End If;
End;

DBMS_ALERT package is commonly used in scenarios where multiple sessions or applications need to be synchronized or notified about specific events or changes in the database. It provides a lightweight and efficient mechanism for inter-session communication within the Oracle Database environment.