Re: DBMS_ALERT: I am flappergastered!!!

In article <7fg3ll$gou_at_jupiter.planet.net>, jared_at_pandora.planet.net says...
> In order to reproduce the problem, start a number of SQL*Plus sessions and
> : execute the SQL script "aloop.sql" in each of these. In another SQL*Plus
> : session signal a number of alerts by running the script "senda.sql". Please
> : note that the "aloop.sql" session will eventually error due to a buffer
> : overflow, or you may stop them by signalling an "END" message text.
>
> : Occasionally it works the way I expect and all SQL*Plus session receives at
> : least one alert - they should usually receive all signalled alerts due to
> : the short time-out values in the DBMS_ALERT.WAITONE invokation. Normally,
> : however, only some or even none of the sessions receives the alerts. As a
> : matter of fact, it seems that the SQL*Plus session which was started as the
> : last, tends to be the one that receives the alerts. I am totally confused as
> : I can see no consistency in this pattern.
>

DBMS_ALERT will deliver an alert to at least one registered session. I think the follow rules sum up how it works but it's early and I'm on my first coffee so your milage may vary:

o When the alert is signaled and at least one session is waiting all sessions that are registered and waiting are alerted.

o When an alert is signaled and no sessions are waiting the alert remains signaled and is delivered to the first session to wait on it.

o There is no guarantee that all registered sessions will reveive any alert.

If you need all waiting sessions to be alerted each time an alert is send you will need to use an alert per waiting session.

Regards,
Bob Received on Wed Apr 21 1999 - 08:08:40 CDT