goal: Does DBMS_JOB recompute the NEXT_DATE interval after or before
executing the procedure or job?
fact: PL/SQL
fix:
The NEXT_DATE is computed AFTER the job gets executed successfully.
The INTERVAL date function is evaluated immediately before a job is executed.
If the job completes successfully, the date calculated from INTERVAL becomes the
new NEXT_DATE. If the INTERVAL date function evaluates to NULL and the job
completes successfully, the job is deleted from the queue.
If a job should be executed periodically at a set interval, use a date
expression similar to ‘SYSDATE + 7’ in the INTERVAL parameter. For example, if
the
execution interval is set to ‘SYSDATE + 7’ on Monday, but for some reason (such
as a network failure) the job is not executed until Thursday, ‘SYSDATE + 7’
evaluates to every Thursday, not Monday.
If the job should automatically execute at a specific time, regardless of the
last execution (for example, every Monday), the INTERVAL and NEXT_DATE
parameters should specify a date expression similar to ‘NEXT_DAY(TRUNC(SYSDATE),
”MONDAY”)’.