Re: DBMS_ALERT: I am flappergastered!!!

One wonders if the problem is SQL*Plus rather than your code. Do you have a Unix box you can try this on, where you are signed on via terminal emulation as vs. client-server?

Regards,
jh

J. Wegener NOSPAM (xjw_at_xdde.xdk) 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

--

Jared Hecker	| HWA Inc. - Oracle architecture and Administration
jared_at_hwai.com	|  ** serving NYC and New Jersey **