Re: DBMS_ALERT: I am flappergastered!!!

DBMS_ALERT is not a true publish and subscribe system. If you issue multiple DBMS_ALERT's simultaneously (through senda.sql) then the receiver will know that an alert has been sent, but it will not know how many, and if I remember correctly, only the last alert is received.

If you want a publish and subscribe mechanism, then consider Oracle Advance Queuing (AQ). It was new in Oracle8 (if i remember correctly), and supposedly heavily improved in Oracle8i, but I have not had the ability to test yet.

Andrew

"J. Wegener NOSPAM" wrote:

> Hello,
>
> I'm having severe problems getting programs based on the DBMS_ALERT package
> working the way I expect. I admit that I may have misunderstood something as
> the supplied on-line documentation is somewhat thin...
>
> Please consider the following two small pieces of code. It should run under
> any user with the privilige of executing the DBMS_ALERT package:
>
> REM ****** senda.sql ********
> set feedback off verify off
> execute dbms_alert.signal('SCOTT', '&1');
> commit;
> REM ******* END *************
>
> REM ****** aloop.sql ********
> set serveroutput on feedback off
> declare
> v_message varchar2(100);
> v_status integer;
> begin
> DBMS_ALERT.REGISTER('SCOTT');
> loop
> dbms_output.put_line('Waiting...');
> DBMS_ALERT.WAITONE('SCOTT', v_message, v_status, 1);
> if v_status = 0 then
> dbms_output.put_line('Received alert: ' || v_message);
> exit when v_message = 'END';
> else
> dbms_output.put_line('TIMEOUT!');
> end if;
> end loop;
> DBMS_ALERT.REMOVE('SCOTT');
> end;
> /
>
> REM ******* END *************
>
> 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.
>
> BTW, I' using Oracle 8.0.5.0.0 on Windows NT SP3 (Client and Server).
>
> Any help would be greatly appreciated, thanks in advance....
>
> Cheers,
> Johan
>
> --
> Johan Wegener
> Dansk Data Elektronik A/S
> Reply to: xjw_at_xdde.xdk
> NOSPAM: Delete xxx from my email address
Received on Mon Apr 19 1999 - 08:05:41 CDT