WAITONE procedure - Wait for a specified alert
The WAITONE procedure waits for a specified alert to occur.
Syntax
Procedure parameters
- name
- An input argument of type VARCHAR(128) that specifies the name of the alert.
- message
- An output argument of type VARCHAR(32672) that contains the message sent by the SIGNAL procedure.
- status
- An output argument of type INTEGER that contains the status code
returned by the procedure. The following values are possible
- 0
- An alert occurred.
- 1
- A timeout occurred.
- timeout
- An input argument of type INTEGER that specifies the amount of time in seconds to wait for the specified alert.
Authorization
EXECUTE privilege on the DBMS_ALERT module.
Examples
Run a CLP script named waitone.clp to receive an alert named alert_test.
waitone.clp:
SET SERVEROUTPUT ON@
CREATE PROCEDURE proc1()
BEGIN
DECLARE v_name VARCHAR(30) DEFAULT 'alert_test';
DECLARE v_msg VARCHAR(80);
DECLARE v_status INTEGER;
DECLARE v_timeout INTEGER DEFAULT 20;
CALL DBMS_ALERT.REGISTER(v_name);
CALL DBMS_OUTPUT.PUT_LINE('Waiting for signal...');
CALL DBMS_ALERT.WAITONE(v_name , v_msg , v_status , v_timeout);
CALL DBMS_OUTPUT.PUT_LINE('Alert name : ' || v_name);
CALL DBMS_OUTPUT.PUT_LINE('Alert msg : ' || v_msg);
CALL DBMS_OUTPUT.PUT_LINE('Alert status : ' || v_status);
CALL DBMS_OUTPUT.PUT_LINE('Alert timeout: ' || v_timeout || ' seconds');
CALL DBMS_ALERT.REMOVE(v_name);
END@
CALL proc1@From a different connection, run a script named signalalert.clp to issue a signal for an alert named alert_test.
signalalert.clp:
SET SERVEROUTPUT ON@
CREATE PROCEDURE proc2
BEGIN
DECLARE v_name VARCHAR(30) DEFAULT 'alert_test';
CALL DBMS_ALERT.SIGNAL(v_name,'This is the message from ' || v_name);
CALL DBMS_OUTPUT.PUT_LINE('Issued alert for ' || v_name);
END@
CALL proc2@The script signalalert.clp results in the following output:
Issued alert for alert_test
The script waitone.clp results in the following output:
Waiting for signal...
Alert name : alert_test
Alert msg : This is the message from alert_test
Alert status : 0
Alert timeout: 20 seconds