The DBMS_ALERT package provides the capability to register for, send, and receive alerts.
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. |
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
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
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.
|
timeout | Specified the amount of time to wait before the alert is generated. Unit: seconds. |
Examples
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...
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
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.
|
timeout | Specifies the amount of time it takes to wait for the alert to be generated. Unit: seconds. |
Examples
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...
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
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.
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;
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...
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);
INSERT INTO dept VALUES (60,'HR','L0S ANGELES');
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