DBMS_ALERT

This package allows one-way communication from one session to another. The alert is sent upon commit, so it is a synchronous signal. If the transaction is rolled back, the alert signal is canceled.

PROCEDURE SIGNAL(

     name IN VARCHAR2,

     message IN VARCHAR2);

An alert name identifies an alert uniquely. Therefore, a particular alert can only be sent by one session at a time. The dbms_alert_info table in the data dictionary records the state of each alert as signaled or not signaled. The SIGNAL procedure is used to set this value to signaled so it will be sent upon commit. If two sessions signal the same alert, the first blocks the second from sending until the first issues a commit and its alert is sent. Then the second alert is sent. The message part of an alert can be up to 1,800 bytes. All sessions registered for a given alert will receive it when it is sent.

PROCEDURE REGISTER(

     name IN VARCHAR2);

This is the first necessary step to receive an alert message. A session can register for any number of alerts.

PROCEDURE WAITONE(

     name IN VARCHAR2,

     message OUT VARCHAR2,

     status OUT INTEGER,

     timeout IN NUMBER default MAXWAIT);

This procedure causes the session to sleep until the specified alert is received or the timeout is reached. If the session issues more than one WAITONE, the next is not reached until the first is resolved.

PROCEDURE WAITANY(

     name OUT VARCHAR2,

     message OUT VARCHAR2,

     status OUT INTEGER,

     timeout IN NUMBER default MAXWAIT);

Identical to WAITONE except that any registered alert is waited for. Only the most recent alert signaled will be received.

PROCEDURE REMOVE(

     name IN VARCHAR2);

Unregisters the named alert.

PROCEDURE SET_DEFAULTS(

     polling_interval IN NUMBER);

The polling interval is used to specify the length of time to wait between checking for alerts. SET_DEFAULTS uses a default of five seconds. Normally Oracle is event-driven and there is no need for a polling loop, but two sets of circumstances make them necessary.

When using the WAITANY procedure, it is possible for one session to issue a blocking signal and not commit for more than one second. WAITANY would miss alerts issued in the meantime without a polling interval. In this case, polling is automatically performed, starting with a 1-second interval and increasing exponentially to 30 seconds. SET_DEFAULTS cannot modify this behavior.

When running in shared mode, a polling loop is needed to check for alerts signaled from other instances. The default is one second and can be set with SET_DEFAULTS.

Here’s some example code for DBMS_ALERT. The first listing sends a message and the second receives it:

-- Here we demonstrate DBMS_ALERT.SIGNAL. 

DECLARE

    Message     VARCHAR2(255);

    AlertName    VARCHAR2(30);

BEGIN



    Message := 'This is a test. This is only a test. If this was a real

emergency this message would be in capital letters and have a lot more

punctuation.’;



    AlertName := 'TestAlert1’;



    DBMS_ALERT.SIGNAL(AlertName, Message);



    -- You must commit in order for the message to be sent.



    COMMIT;



END;

/

Now that we’ve sent an alert, let’s set up a receiver:

SET SERVEROUTPUT ON





DECLARE



    MessageBuffer     VARCHAR(255);

    AlertName    VARCHAR(30);

    AlertStatus    INTEGER;



BEGIN



    AlertName := 'TestAlert1’;



    -- Step one is to notify the server that you’re interested in the

    -- alert.



    DBMS_ALERT.REGISTER(AlertName);



    -- Step two is to wait as long as you’re willing for it to be sent.



    DBMS_ALERT.WAITONE(AlertName, MessageBuffer, AlertStatus, 10);



    -- If you don’t get one, let us know.



    IF AlertStatus = 1 THEN

        DBMS_OUTPUT.PUT_LINE('Timed out waiting for the alert.’);

    END IF;



    -- If you do get one, print it.



    DBMS_OUTPUT.PUT_LINE(MessageBuffer);



END;

/