Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> DBMS_ALERT: I am flappergastered!!!
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;
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 - 07:40:27 CDT