Re: How to be notified when a job has finished

On Feb 28, 11:20 am, Anis <anisb..._at_gmail.com> wrote:
> On Feb 28, 4:57 pm, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
>
>
>
>
>
> > On Feb 27, 9:33 am, Anis <anisb..._at_gmail.com> wrote:
>
> > > 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!- Hide quoted text -
>
> > > - Show quoted text -
>
> > You would have to have the job signal the alert so a minor change is
> > required.  See either the Supplied PL/SQL Package and Types manual or
> > the PL/SQL Packages and Types manual for your release (9i, 10g) entry
> > for DBMS_ALERT.  Probably only a one line call will be necessary.
>
> > If you do not have access to the code or the developers then this
> > option probably is not usuable to you unless you can just call the
> > supplied code in a wrapper procedure: call existing code, call
> > dbms_alert.
>
> > HTH -- Mark D Powell --
>
> Well, I have access to all the code:
>
> the MV consist of : select * someView
> when any table from the someView is updated, a trigger call a
> procedure
> the procedure body consist of:
>
>            dbms_job.submit( jobno, ''dbms_mview.refresh(...)'' );
>
> If I put directly dbms_mview.refresh(...) instead of the submit, my
> application hangs until the MV is completed (which can last 5 or 10
> minutes)
> So with the submit my app is not blocked, however I want to chain some
> MV refreshes (so I need to know when a job has finished)
>
> Any idea ?
>
> thanx a lot- Hide quoted text -
>
> - Show quoted text -

Yes, write a mview job manager routine. It will issue a call to a wrapper routine that make the dbms_mview.refresh call and immediately upon return issues a dbms_alert that the job has finished. Meanwhile the job manager routine has issued a dbms_alert wait call for the alert that the submitted wrapper will issue. Upon receipt of the wait the job manager can then issue the next scheduled mv refresh.

The job manager will suspecd while it waits on the altert but once received it will continue so you have basically just created a system to single thread the job stream.

Maybe an easier way might be just to call a routine that issues the mv refresh then upon return submits the next job which does the same two actions. This would be less flexible than making a mv job manager task that was table driven for what gets submitted in what order but if is easy to code.

HTH -- Mark D Powell -- Received on Sat Mar 01 2008 - 08:33:51 CST