Re: dbms_alert triggered processing

On 19 Nov., 19:13, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> 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 --

Thanks. What would be the advantage of this?

regards,
Stephan Received on Tue Nov 20 2007 - 01:54:04 CST