 |
|
Oracle Tips by Burleson |
Chapter 3 - Time-Based Job Scheduling
Complex Date Rules
for Job Execution
This issue becomes even more noticeable on shorter repeat intervals
such as hourly runs. This behavior can be prevented by always
defining PL/SQL expressions that result in a specific time, rather
than one relative to the current time. This is typically done using
the trunc and round functions to remove the variable
components. For example, ‘trunc(sysdate) + 1 + 6/24’ is always 06:00
tomorrow morning no matter what time it is evaluated because the
time component has been truncated. The earlier examples regularly
make use of the trunc function for the same reason.
The calendar syntax does not suffer from the
problem of sliding schedules as the repeat intervals it defines are
always time specific. If a component of the calendar string is not
defined explicitly, it is defaulted using values from the start date
specified when the job or schedule was defined. For example, a
schedule with a start date of 01-JAN-2004 09:45:31 and a calendar
string with no byminute clause would actually be assigned
byminute=45. As a result, every next_run_date evaluated
using this schedule would have a value of 45 minutes past the hour.
Sometimes it is either not possible or very
difficult to define a repeat interval using the calendar syntax or a
PL/SQL expression. In these situations, it might be easier to use a
database function which returns a date or timestamp as required.
The my_schedule_function.sql script creates a function which
returns a different time interval depending on the contents of the
database.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
