stopping JOBS and then restarting via PL/SQL Dev

[usertale]

Hello...

How do I stop a Oracle Job in PL/SQL Dev? And then how do I restart the job? Can I just use the 'broken' check box?

thx.

 

[Marco Kalter]

There is no function to stop a job. This is a DBMS_JOB restriction. You can kill the corresponding session of course.

 

[usertale]

Actually, if you check the 'Broken' checkbox and hit the 'Apply' button ,the job will stop. Reverse the process and the job will start rerunning.
Thx.

 

[Claus Pedersen]

To usertale:

If a job is running for a long time, it does not stop even if it is broken. The job will continue to run (for instance in a never-ending loop) until the session is killed from outside. The 'broken' state just prevents if from starting up again, once finished.

It is always a good idea for the programmer to check inside the job itself (for instance at the beginning of a loop) whether the job is broken. If the job is broken, the program must be exited. In this way the code will eventually stop when the code inside the job finds out that it has been broken and the session will terminate.

 

[usertale]

You mentioned "It is always a good idea for the programmer to check inside the job itself (for instance at the beginning of a loop) whether the job is broken. If the job is broken, the program must be exited. In this way the code will eventually stop when the code inside the job finds out that it has been broken and the session will terminate."

Do you have some example PL/SQL code that does this? I think it is an excellent idea and I would like to see how one implements it.

Thank u.

 

[rbrooker]

Something else to consider, if you mark a job as broken while it is running and then commit the transaction, the job is marked as broken.

But.

When the job finishes running, the broken flag is cleared by the database ( the job finished successfully, as far as oracle is concerned, the job is not broken and it sets the broken flag accordingly ).

 

[usertale]

Never got any feedback from the following circa 1/8/08:
=========================================
You mentioned "It is always a good idea for the programmer to check inside the job itself (for instance at the beginning of a loop) whether the job is broken. If the job is broken, the program must be exited. In this way the code will eventually stop when the code inside the job finds out that it has been broken and the session will terminate."

Do you have some example PL/SQL code that does this? I think it is an excellent idea and I would like to see how one implements it.
=============================================
Any ideas?

 

[rbrooker]

this select will return the broken flag for a job based on the SID of the session running the job.

select broken
from dba_jobs
where job = (select /*+ RULE */
              job
             from dba_jobs_running
             where sid = userenv('SID'))

 

[rbrooker]

you will need to raise an error to stop it and maintain the status of the broken flag.

 

[usertale]

An example would be to do something like the followning job PL/SQL:

declare
v_job_status VARCHAR2(1) := 'N';
begin
-- check job status
select broken
into v_job_status
from dba_jobs
where job =
(select /*+ RULE */ job
from dba_jobs_running
where sid = userenv('SID'));
if v_job_status = 'Y' then
raise xx_job_broke;
else
-- do work for job
select ....
end if;

exception
when xx_job_broke then
dbms_output.put_line('Job down');
end;