Re: DBMS_JOB - BROKEN FLAG ISSUE

On 12/18/06, Richard J. Goulet <rgoulet_at_kanbay.com> wrote:
> I believe the status is set by either the dbms_job
> procedure or the last execution whichever terminates last.

Actually I've run this in 10.2.0.2:

dellera_at_ORACLE10> exec dbms_job.submit (:j, what=>'begin execute immediate ''alter session set events=''''10046 trace name context forever, level 12'''' ''; end;', in
terval=>'sysdate+1');

And immediately after the execution of the "what" pl/sql block, I see:

EXEC #5 (snip) <-- cursor #5 is "what"
BINDS #4:
kkscoacd
 Bind#0
  (snip)
  value=0
(snip)
PARSING IN CURSOR #4 (snip)
update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2,  next_date = greatest(:3, sysdate),
total=total+(sysdate-nvl(this_date,sysdate)) where job=:4 END OF STMT
EXEC #4(snip)

That is, "flag" is set to 0, without checking its value beforehand.

Broken is (from dba_jobs definition):
select

    (snip)
    decode(mod(FLAG,2),1,'Y',0,'N','?') BROKEN,     (snip)
  from sys.job$ j

So flag=0 => broken='N'.

It makes sense - if "what" terminated successfully, the job is not broken by definition, and whatever is recorded in sys.job$ is history :)

-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l