Re: DBMS_JOB - BROKEN FLAG ISSUE

On 12/16/06, Subbiah, Nagarajan <Nagarajan.Subbiah_at_aetn.com> wrote:
> One of our developer has reported a problem that when the broken flag is
> set to TRUE from the application ( connection comes from the webserver )
> for a scheduled job, its not being set. I tried the following from the
> SQLPlus
>
> dbms_job.broken(<jobno>,TRUE);
>
> The broken flag is set to 'Y'.
>
> Then I gave the 'commit;' After the commit, The broken flag was set as
> 'N'.

It doesn't reproduce on 10.2.0.2 on windows XP: dellera_at_ORACLE10> variable j number
dellera_at_ORACLE10> exec dbms_job.submit (:j, what=>'begin null; end;', interval=>'sysdate+1');

PL/SQL procedure successfully completed.

dellera_at_ORACLE10> commit;

Commit complete.

dellera_at_ORACLE10> select broken from user_jobs;

BRO

---
N

dellera_at_ORACLE10> exec dbms_job.broken (:j, true);

PL/SQL procedure successfully completed.

dellera_at_ORACLE10> select broken from user_jobs;

BRO
---
Y

dellera_at_ORACLE10> commit;

Commit complete.

dellera_at_ORACLE10> select broken from user_jobs;

BRO
---
Y

Could it be that you set the broken flag when the job was running,
then the job ended, tried to update the status row, found it locked
by you, and so only immediately after the commit, was able to
update the status row thus overwriting your broken value ?

BTW when you exit, sqlplus commits by default, so your scenarios
are identical - maybe the difference was in the job running or not.
-- 
Alberto Dell'Era
"Per aspera ad astra"
--
http://www.freelists.org/webpage/oracle-l