Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> DBMS_ALERT: I am flappergastered!!!

DBMS_ALERT: I am flappergastered!!!

From: J. Wegener NOSPAM <xjw_at_xdde.xdk>
Date: Mon, 19 Apr 1999 14:40:27 +0200
Message-ID: <7ff86j$qg5$1@news101.telia.com>


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 - 07:40:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US