Re: How to be notified when a job has finished

On 27 fév, 15:26, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Feb 27, 8:09 am, Anis <anisb..._at_gmail.com> wrote:
>
>
>
> > On 27 fév, 13:52, "news.verizon.net" <kenned..._at_verizon.net> wrote:
>
> > > <anisb..._at_gmail.com> wrote in message
>
> > >news:dd7aa973-4322-455b-938a-ba922057b4d6_at_d21g2000prf.googlegroups.com...> Hi,
>
> > > > I have triggers that submit jobs to the user_jobs via this command:
>
> > > > dbms_job.submit( jobno, 'dbms_mview.refresh(''MY_MV'', ''C'');' );
>
> > > > Is there a possibility to beeing informed when the job finishes ?
> > > > (An alternate and not elegant solution would be to poll every minute
> > > > the user_jobs queue and check if the
> > > > jobno is still there)
>
> > > > Can anyone help ?
>
> > > > Thanx,
> > > > Anis
>
> > > How would you want the notification? You could wrap the procedure and call
> > > that and have that procedure do someting when the procedure fiunishes.
>
> > > create or replace procedure mywrapped(arg1 in varchar2, arg2 in varchar2) as
> > > begin
> > > dbms_mview.refresh(arg1 , arg2);
> > > insert into sometble values('job is done');
> > > commit;
> > > end;
> > > /
>
> > I use a procedure that is called by a trigger
>
> > The reason why I use job_submit instead of dbms_mview_refresh is that
> > I want to control the number of refreshes
> > (so I don't have more than 3 requests pending in the queue)
> > So the Proc look like:
>
> > CREATE OR REPLACE
> > PROCEDURE "MPA_MY_MV"
> > AS jobno number;
> > count_update number;
> > BEGIN
> > select count(*) into count_update from user_jobs where
> > lower(what) like lower( 'MY_MV' );
> > IF count_update <= 2 THEN
> > dbms_job.submit( jobno, 'dbms_mview.refresh(''MY_MV'',
> > ''C'');' );
> > commit;
> > END IF;
> > END;- Hide quoted text -
>
> > - Show quoted text -
>
> Instead of using an insert into a table in the wrapper to identify a
> job is done you might consider using the dbms_alert package to signal
> another program waiting on the alert if this is a non-RAC
> environment. The waiter could be a monitoring screen or another
> session that someone views.
>
> In an OPS and later RAC environment we have had numerous problems when
> the signaler and waiter for an alter were not in the same instance
> which is why I specified a non-RAC environment.
>
> HTH -- Mark D Powell --

Ok this is a non RAC environment,
how can I setup the Alert ?
(having the job number from the queue)

thanx! Received on Wed Feb 27 2008 - 08:33:16 CST