dbms_alert triggered processing

From: steph <stephan0h_at_yahoo.de>
Date: Mon, 19 Nov 2007 09:40:25 -0800 (PST)
Message-ID: <0d07e21b-45f2-4a60-aecf-05aa52be0a1d@n20g2000hsh.googlegroups.com>

Hi Group,

I'm thinking about an efficient and elegant solution for the following problem (Oracle 9i):

I've got 3 tables that are filled once a day by some process outside of the database. I don't exactly know, when this happens, but after all 3 of them are filled up, I want some processing to take place, i.e. start up a procedure.

I thought about utilizing DBMS_ALERT for this task, that is, having a trigger on each of these tables that signals an alert. The procedure would then need to wait until all 3 of these alerts have arrived.

procedure postprocess IS

  l_message varchar2(100);
  l_status  varchar2(100);
  l_tab1    boolean:=false;
  l_tab2    boolean:=false;
  l_tab3    boolean:=false;

begin
  dbms_alert.register('my.alert');
  loop

    dbms_alert.waitone('my.alert', l_message, l_status);
    if l_message='t1' then l_tab1:=true; end if;
    if l_message='t2' then l_tab2:=true; end if;
    if l_message='t3' then l_tab3:=true; end if;
    if l_tab1=true and l_tab2=true and l_tab3=true then
      exit;

    end if;
  end loop;

• postprocessing starts here ... end; /

Are there better ways to handle this task? Any caveats with this solution?

Thanks,
Stephan Received on Mon Nov 19 2007 - 11:40:25 CST