SQL & PL/SQL

Hope nobody minds, but, in the hope that it might help the similarly misguided, I thought I would share an unexpected (to me) behavior of DBMS_ALERT.

Consider the following:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2      strMessage VARCHAR2(2000);
  3      iStatus INTEGER;
  4  
  5  BEGIN
  6      DBMS_ALERT.REGISTER('TEST');
  7  
  8      DBMS_ALERT.SIGNAL('TEST', NULL);
  9      COMMIT;
 10  
 11      DBMS_ALERT.WAITONE('TEST', strMessage, iStatus, 0);
 12      DBMS_OUTPUT.PUT_LINE('Status returned by first call = ' || iStatus);
 13  
 14      DBMS_ALERT.WAITONE('TEST', strMessage, iStatus, 0);
 15      DBMS_OUTPUT.PUT_LINE('Status returned by second call = ' || iStatus);
 16  
 17      DBMS_ALERT.REMOVE('TEST');
 18  END;
 19  /
Status returned by first call = 0
Status returned by second call = 1

PL/SQL procedure successfully completed.

SQL>

Note that status = 0 implies that the alert "occurred", and status = 1 implies that the wait timed out (in this case immediately) before the alert "occurred".

So, it appears that, once the session detects that the alert has been signalled (in the first WAITONE call), it "resets" the signal.

I can reproduce the behavior when the SIGNAL and WAITONEs are in different sessions, and in 10.1.0.4 and XE.

The "PL/SQL Packages and Types Reference" doesn't specify this behavior. However, Metalink Note 67544.1 says, "If the alert was signalled since the register or last waitone/waitany, then this call will return immediately." While this doesn't say, "If the alert was not signalled since the register or last waitone/waitany, then this call will not return immediately," it appears that's how it works.

I wish they would document this stuff more clearly...