Re: How to be notified when a job has finished

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 -- Received on Wed Feb 27 2008 - 08:26:49 CST