Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I wanted to create a job that will run tomorrow at noon and recur every day at noon, so I executed this script:

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'BEGIN  MyProcedure();  END;'
   ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)+12/24'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
  SYS.DBMS_JOB.BROKEN
   (job    => X,
    broken => FALSE);
COMMIT;
END;
/

Job Number is: 131

When I looked at the status of job 131 it was executing already! THIS_DATE was 23/04/2012 13:16:12

Why did this job execute immediately?

Was it because of the SYS.DBMS_JOB.BROKEN call? I used TOAD to generate this script, and I changed the default broken => TRUE value that TOAD generated to be FALSE instead.

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

Setting the job to "unbroken" reschedules it:

    SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X
  6     ,what      => 'BEGIN  NULL;  END;'
  7     ,next_date => to_date('24/04/2012 12:00:00','dd/mm/yyyy hh24:mi:ss')
  8     ,interval  => 'TRUNC(SYSDATE+1)+12/24'
  9     ,no_parse  => FALSE
 10    );
 11    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12
 13  COMMIT;
END;
 14   15  /
Job Number is: 5

PL/SQL procedure successfully completed.


SQL> alter session set nls_date_format='DDMMYYYY HH24:MI:SS';

Session altered.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   24042012 12:00:00 N

SQL> BEGIN
  2  SYS.DBMS_JOB.BROKEN(job=>5,broken=>FALSE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL>

Once you hit commit, the job scheduler sees the job and executes it:

SQL> select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5                   23042012 21:55:49 N

SQL> commit;

Commit complete.

SQL>  select job,last_date,next_date,broken from dba_jobs where job=5;

       JOB LAST_DATE         NEXT_DATE         B
---------- ----------------- ----------------- -
         5 23042012 21:59:06 24042012 12:00:00 N

SQL>

Moral of the story: If creating a new job, don't include a call to DBMS_JOB.BROKEN().

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.