Re: dbms_alert triggered processing

On Nov 19, 12:40 pm, steph <stepha..._at_yahoo.de> wrote:
> 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

If the data is deleted (truncated) after use you might just have the process wake up once every N minutes and select a single row from each table. If all three selects return a row then all necessary data for processing has been received. A job scheduler can be used to only run this process within a certain time frame.

HTH -- Mark D Powell -- Received on Mon Nov 19 2007 - 12:13:19 CST