Re: How to be notified when a job has finished

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; Received on Wed Feb 27 2008 - 07:09:34 CST