Why does DBMS_JOB.SUBMIT execute immediately when NEXT_DATE is in the future?

Question

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.

Answer 1

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().