more dbms_alert problems

Thanks to all who replied previously about the dbms_alert package. I was able to get my trigger defined and the procedure created.

Now I am having a problem getting my alert to fire. Maybe I'm doing something wrong or maybe I just don't understand how alerts work. Here is what I am doing so maybe someone can give me some clue on what is wrong or give me a good example of how to create and fire an alert.

To start with I create a trigger on the scott/tiger dept table with this section of PL/SQL code:

create trigger depttrig after insert or update or delete on dept begin
  dbms_alert.signal('dept_table_alert','the dept table has changed'); end;

Now I register my interest in the alert with the following: begin
  dbms_alert.register('dept_table_alert'); end;

Now I try to create a procedure to continously loop through printing the values in the dept table when anything changes.

create procedure mike as
  cursor dept_cursor is select deptno, dname, loc from dept;   status integer;
  msg varchar2(200);
begin
<<readagain>>
  for dept_record in dept_cursor loop
    dbms_output.put_line(to_char(dept_record.deptno) || ' ' ||       dept_record.dnam || ' ' || dept_record.loc );   end loop;
  dbms_alert.waitone('dept_table_alert',msg,status,15);   if status = 0 then
    goto readagain;
  else
    raise_application_error(-20000,'my error text');   end if;
end;

Having gotten all of this set up I now get into SQL*Plus in 2 different windows. In window 1 I set serveroutput on so that my dbms_output stuff will work and then execute the mike procedure. I am surprised because when I execute the mike procedure I don't get anything displayed immediately like I thought I would since I do a dbms_output BEFORE my call to dbms_alert.waitone. What happens is that after my timeout period (15 secs) has expired the results come back from my initial dbms_output loop and then 'my error text' gets displayed. It does not go back through the <<readagain>> loop like I was hoping.

If, after executing procedure mike in window 1, I go do window 2 and update the dept table and commit BEFORE my timeout period expires nothing happens in window 1. After the timeout period in window 1 it still returns my 'my error text' error.

Any help here would be appreciated.

Mike Campbell                         Phone     : (205)730-3623
Senior Customer Application Engineer  Internet  : mlcampb1_at_ingr.com
Intergraph Corp.                      Fax       : (205)730-3300
Huntsville, AL 35894-0001             Mailstop  : GD3002