Re: How to be notified when a job has finished

On Feb 28, 4:57 pm, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:
On Feb 27, 9:33 am, Anis <anisb...@xxxxxxxxx> wrote:



On 27 fév, 15:26, Mark D Powell <Mark.Pow...@xxxxxxx> wrote:

On Feb 27, 8:09 am, Anis <anisb...@xxxxxxxxx> wrote:

On 27 fév, 13:52, "news.verizon.net" <kenned...@xxxxxxxxxxx> wrote:

<anisb...@xxxxxxxxx> wrote in message

news:dd7aa973-4322-455b-938a-ba922057b4d6@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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





.

Relevant Pages

  • Re: How to be notified when a job has finished
    ... (so I don't have more than 3 requests pending in the queue) ... AS jobno number; ... The waiter could be a monitoring screen or another ... Ok this is a non RAC environment, ...
    (comp.databases.oracle.server)
  • Re: How to be notified when a job has finished
    ... (An alternate and not elegant solution would be to poll every minute ... (so I don't have more than 3 requests pending in the queue) ... AS jobno number; ...
    (comp.databases.oracle.server)