Re: How to be notified when a job has finished

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 Received on Thu Feb 28 2008 - 10:20:09 CST