The DBMS_ALERT package provides the capability to register for, send, and receive alerts.

Table 1. DBMS_ALERT function and stored procedures
Function or stored procedure Return value type Description
REGISTER(name) N/A Uses the current session to receive the notification information of the specified alert.
REMOVE(name) N/A Cancels receiving the notification information of the specified alert.
REMOVEALL N/A Deletes all alerts for the specified session from the registration list.
SIGNAL(name, message) N/A Generates the alert configured with the specified name.
WAITANY(name OUT, message OUT, status OUT, timeout) N/A Waits for the registered alert to be generated.
WAITONE(name, message OUT, status OUT, timeout) N/A Waits for the specified alert to be generated.
PolarDB allows a maximum of 500 concurrent alerts. To specify the maximum number of concurrent alerts the system allows, you can set dbms_alert.max_alerts in the postgresql.conf file. When you set dbms_alert.max_alerts, you can use your editor to open the postgresql.conf file in the /opt/PostgresPlus/9. 3AS/data path, and set dbms_alert.max_alerts based on the following example:
dbms alert.max alerts = alert count
Note alert_count specifies the maximum number of concurrent alerts. The default value of dbms_alert.max_alerts is 100. To disable this feature, set dbms_alert.max_alerts to 0.

To ensure that dbms_alert.max_alerts works normally, make sure that the custom_variable_classes value contains dbms_alert.

custom variable classes = 'dbms_alert, ...'

You must restart the server after you edit the postgresql.conf file.

REGISTER

The current session can receive the notification information of the specified alert.

Syntax

REGISTER(name VARCHAR2) 

Parameters

Parameter Description
name Specifies the name of the alert to be registered.

Examples

The following anonymous block is used to register an alert named alert_test:
DECLARE
    v_name           VARCHAR2(30) := 'alert_test';
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER(v_name);
    DBMS_OUTPUT.PUT_LINE('Registered for alert ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITONE(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVE(v_name);
END;
A similar output is returned:
Registered for alert alert_test
Waiting for signal...

REMOVE

You can cancel the notification information of the specified alert.

Syntax

REMOVE(name VARCHAR2)

Parameters

Parameter Description
name Specifies the name of the alert to be unregistered.

REMOVEALL

You can remove all alerts for the specified session from the list.

Syntax

REMOVEALL

SIGNAL

You can specify the name of the alert to be generated.

Syntax

SIGNAL(name VARCHAR2, message VARCHAR2)

Parameters

Parameter Description
name Specifies the name of the alert.
message Specifies the information related to the alert.

Examples

The following anonymous block is used to send an alert named alert_test:
DECLARE
    v_name   VARCHAR2(30) := 'alert_test';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END; 
A similar output is returned:
Issued alert for alert_test  

WAITANY

You can wait for a registered alert to be generated.

Syntax

WAITANY(name OUT VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout NUMBER)

Parameters

Parameter Description
name Specifies the name of the alert.
message Specifies the information related to the alert.
status Specifies the returned HTTP status code.
  • 0: The system generates an alert.
  • 1: The alert times out.
timeout Specified the amount of time to wait before the alert is generated. Unit: seconds.

Examples

The following anonymous block uses the WAITANY stored procedure to receive an alert named alert_test or any_alert:
DECLARE
    v_name           VARCHAR2(30);
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER('alert_test');
    DBMS_ALERT.REGISTER('any_alert');
    DBMS_OUTPUT.PUT_LINE('Registered for alert alert_test and any_alert');
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITANY(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVEALL;
END;   
A similar output is returned:
Registered for alert alert_test and any_alert
Waiting for signal...    
The following anonymous block is used to issue a signal for any_alert:
DECLARE
    v_name   VARCHAR2(30) := 'any_alert';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END;
A similar output is returned:
Issued alert for any_alert
The following output shows that control returns the first anonymous block and the remaining code is executed:
Registered for alert alert_test and any_alert
Waiting for signal...
Alert name   : any_alert
Alert msg    : This is the message from any_alert
Alert status : 0
Alert timeout: 120 seconds

WAITONE

You can wait for the specified alert to be generated.

Syntax

WAITONE(name VARCHAR2, message OUT VARCHAR2, status OUT INTEGER, timeout NUMBER)

Parameters

Parameter Description
name Specifies the name of the alert.
message Specifies the message sent by the SIGNAL stored procedure.
status Specifies the returned HTTP status code.
  • 0: The system generates an alert.
  • 1: The alert times out.
timeout Specifies the amount of time it takes to wait for the alert to be generated. Unit: seconds.

Examples

The following anonymous block is similar to the one used in the WAITANY example except that the WAITONE stored procedure is used to receive the alert named alert_test.
DECLARE
    v_name           VARCHAR2(30) := 'alert_test';
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 120;
BEGIN
    DBMS_ALERT.REGISTER(v_name);
    DBMS_OUTPUT.PUT_LINE('Registered for alert ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    DBMS_ALERT.WAITONE(v_name,v_msg,v_status,v_timeout);
    DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
    DBMS_ALERT.REMOVE(v_name);
END;
A similar output is returned:
Registered for alert alert_test
Waiting for signal...
The following anonymous block issues a signal for alert_test:
DECLARE
    v_name   VARCHAR2(30) := 'alert_test';
BEGIN
    DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END;
A similar output is returned:
Issued alert for alert_test
The following output shows that the first session is alerted, control returns the first anonymous block, and the remaining code is executed:
Registered for alert alert_test
Waiting for signal...
Alert name   : alert_test
Alert msg    : This is the message from alert_test
Alert status : 0
Alert timeout: 120 seconds

Comprehensive example

The following example uses two triggers to send alerts when the dept table or the emp table is changed. An anonymous block listens for these alerts and displays messages when an alert is received.

The triggers on the dept and emp tables are defined:
CREATE OR REPLACE TRIGGER dept_alert_trig
    AFTER INSERT OR UPDATE OR DELETE ON dept
DECLARE
    v_action        VARCHAR2(25);
BEGIN
    IF INSERTING THEN
        v_action := ' added department(s) ';
    ELSIF UPDATING THEN
        v_action := ' updated department(s) ';
    ELSIF DELETING THEN
        v_action := ' deleted department(s) ';
    END IF;
    DBMS_ALERT.SIGNAL('dept_alert',USER || v_action || 'on ' ||
        SYSDATE);
END;

CREATE OR REPLACE TRIGGER emp_alert_trig
    AFTER INSERT OR UPDATE OR DELETE ON emp
DECLARE
    v_action        VARCHAR2(25);
BEGIN
    IF INSERTING THEN
        v_action := ' added employee(s) ';
    ELSIF UPDATING THEN
        v_action := ' updated employee(s) ';
    ELSIF DELETING THEN
        v_action := ' deleted employee(s) ';
    END IF;
    DBMS_ALERT.SIGNAL('emp_alert',USER || v_action || 'on ' ||
        SYSDATE);
END;
The following anonymous block is executed in a session while the dept and emp tables are being updated in other sessions:
DECLARE
    v_dept_alert     VARCHAR2(30) := 'dept_alert';
    v_emp_alert      VARCHAR2(30) := 'emp_alert';
    v_name           VARCHAR2(30);
    v_msg            VARCHAR2(80);
    v_status         INTEGER;
    v_timeout        NUMBER(3) := 60;
BEGIN
    DBMS_ALERT.REGISTER(v_dept_alert);
    DBMS_ALERT.REGISTER(v_emp_alert);
    DBMS_OUTPUT.PUT_LINE('Registered for alerts dept_alert and emp_alert');
    DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
    LOOP
        DBMS_ALERT.WAITANY(v_name,v_msg,v_status,v_timeout);
        EXIT WHEN v_status != 0;
        DBMS_OUTPUT.PUT_LINE('Alert name   : ' || v_name);
        DBMS_OUTPUT.PUT_LINE('Alert msg    : ' || v_msg);
        DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
        DBMS_OUTPUT.PUT_LINE('------------------------------------' ||
            '-------------------------');
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
    DBMS_ALERT.REMOVEALL;
END;
A similar output is returned:
Registered for alerts dept_alert and emp_alert
Waiting for signal...
User mary performs the following operations on the dept and emp tables:
INSERT INTO dept VALUES (50,'FINANCE,,,CHICAG0');
INSERT INTO emp (empno,ename,deptno) VALUES (9001,'J0NES',50);
INSERT INTO emp (empno,ename,deptno) VALUES (9002,'ALICE',50);           
User john performs the following operations on the dept table:
INSERT INTO dept VALUES (60,'HR','L0S ANGELES');
The following example shows the output displayed by the anonymous block that receives the signals from the triggers:
Registered for alerts dept_alert and emp_alert
Waiting for signal...
Alert name   : dept_alert
Alert msg    : mary added department(s) on 25-OCT-07 16:41:01
Alert status : 0
-------------------------------------------------------------
Alert name   : emp_alert
Alert msg    : mary added employee(s) on 25-OCT-07 16:41:02
Alert status : 0
-------------------------------------------------------------
Alert name   : dept_alert
Alert msg    : john added department(s) on 25-OCT-07 16:41:22
Alert status : 0
-------------------------------------------------------------
Alert status : 1