Use DBMS_ALERT to register for, send, and receive alerts - PolarDB

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	Data type of the return value	Description
REGISTER(name)	N/A	Uses the current session to receive notifications for the specified alert.
REMOVE(name)	N/A	Cancels receiving notifications for the specified alert.
REMOVEALL	N/A	Removes all alerts for the current 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 any registered alert to be generated.
WAITONE(name, message OUT, status OUT, timeout)	N/A	Waits for the specified alert to be generated.

Usage

REGISTER

This function or stored procedure uses the current session to receive notifications for the specified alert.

Syntax

REGISTER(name VARCHAR2)

Parameters

Parameter	Description
name	The name of the alert for which you want to register.

Example

The following anonymous block is used to register for 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;

Sample result:

Registered for alert alert_test
Waiting for signal...

REMOVE

This function or stored procedure cancels receiving notifications for the specified alert.

Syntax

REMOVE(name VARCHAR2)

Parameters

Parameter	Description
name	The name of the alert from which you want to unregister.

REMOVEALL

This function or stored procedure removes all alerts for the current session from the registration list.

Syntax

REMOVEALL

SIGNAL

This function or stored procedure generates the alert configured with the specified name.

Syntax

SIGNAL(name VARCHAR2, message VARCHAR2)

Parameters

Parameter	Description
name	The name of the alert.
message	The message related to the alert.

Example

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;

Sample result:

Issued alert for alert_test

WAITANY

This function or stored procedure waits for any registered alert to be generated.

Syntax

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

Parameters

Parameter	Description
name	The name of the alert.
message	The message related to the alert.
status	The returned status code. Valid values: 0: The system generates an alert. 1: The alert times out.
timeout	The maximum amount of time to wait for the alert to be generated. Unit: seconds.

Example

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;

Sample result:

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;

Sample result:

Issued alert for any_alert

The following output shows that the control process 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

This function or stored procedure waits for the specified alert to be generated.

Syntax

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

Parameters

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

Example

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;

Sample result:

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;

Sample result:

Issued alert for alert_test

The following output shows that the first session is alerted, the control flow 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 or emp table is changed. An anonymous block listens to these alerts and displays messages when an alert is received.

Prepare basic data.

CREATE TABLE dept (
    deptno          NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
    dname           VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
    loc             VARCHAR2(13)
);

CREATE TABLE emp (
    empno           NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
    ename           VARCHAR2(10),
    job             VARCHAR2(9),
    mgr             NUMBER(4),
    hiredate        DATE,
    sal             NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
    comm            NUMBER(7,2),
    deptno          NUMBER(2) CONSTRAINT emp_ref_dept_fk
                        REFERENCES dept(deptno)
);

GRANT ALL ON emp TO PUBLIC;
GRANT ALL ON dept TO PUBLIC;

INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO dept VALUES (20,'RESEARCH','DALLAS');
INSERT INTO dept VALUES (30,'SALES','CHICAGO');
INSERT INTO dept VALUES (40,'OPERATIONS','BOSTON');

INSERT INTO emp VALUES (7369,'SMITH','CLERK',7902,'17-DEC-80',800,NULL,20);
INSERT INTO emp VALUES (7499,'ALLEN','SALESMAN',7698,'20-FEB-81',1600,300,30);
INSERT INTO emp VALUES (7521,'WARD','SALESMAN',7698,'22-FEB-81',1250,500,30);
INSERT INTO emp VALUES (7566,'JONES','MANAGER',7839,'02-APR-81',2975,NULL,20);
INSERT INTO emp VALUES (7654,'MARTIN','SALESMAN',7698,'28-SEP-81',1250,1400,30);
INSERT INTO emp VALUES (7698,'BLAKE','MANAGER',7839,'01-MAY-81',2850,NULL,30);
INSERT INTO emp VALUES (7782,'CLARK','MANAGER',7839,'09-JUN-81',2450,NULL,10);

CREATE USER mary IDENTIFIED BY password;
CREATE USER john IDENTIFIED BY password;

Execute the following statements to define triggers on the dept and emp tables:

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;

Execute the following anonymous block in a session:

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;

Sample result:

Registered for alerts dept_alert and emp_alert
Waiting for signal...

Update the dept and emp tables in other sessions.

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');
```

In this case, the session in Step 3 displays the output produced 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