Re: DBMS_JOB.SUBMIT(:job_no.... Question

Arthur wrote:
>
> Hi,
> I am trying schedule this job and run it daily at ~ 2AM
> But from user_jobs the next execution will exactly in 24 hours after running
> this job.
> Why it happens?
>
> SQL> variable job_no number;
> SQL> BEGIN
> 2 DBMS_JOB.SUBMIT(:job_no, 'run_reports;', trunc(sysdate)+9/24 ,
> 'SYSDATE+1');
> 3 commit;
> 4 END;
> 5 /
>
> PL/SQL procedure successfully completed.
>
> SQL> print job_no;
>
> JOB_NO
> ---------
> 12
>
> SQL> SELECT TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
>
> TO_CHAR(
> --------
> 05:08:35
>
> SQL> execute dbms_job.run(12);
>
> PL/SQL procedure successfully completed.
>
> SQL> SELECT job, next_date, next_sec, failures, broken
> 2 FROM user_jobs;
>
> JOB NEXT_DATE NEXT_SEC FAILURES B
> --------- --------- -------- --------- -
> 12 14-MAR-00 17:09:28 0 N
> !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Because your 'next' is set to 'SYSDATE + 1' which is 1 day past the current moment. Thus when you run at outside the normal schedule, you "corrupt" the next value.

Try 'trunc(sysdate)+1+2/24' for "2am the next day"

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse