12.7. Using Schedules

Schedules define when jobs run as well as when windows are opened. (Windows will be covered later in this chapter.) Like jobs and programs, schedules are stored objects and follow all the same naming requirements. By saving schedules as independent objects, they can be used by multiple jobs.

Schedules define not only when a job will start, but also how often the job will be repeated. This is known as the repeat interval. Oracle's Scheduler offers two ways to define the interval: using PL/SQL expressions or using the powerful new calendaring syntax introduced in Oracle 10g.

In the following sections, you will learn which attributes define a schedule object. You will learn how to create and drop schedules. You will also learn how to define repeat intervals using the calendaring syntax.

12.7.1. Schedule Attributes

Schedule objects have a specific set of attributes that you can set to define the characteristics of the schedule. These attributes can be set at creation time through the following CREATE_SCHEDULE procedure parameters:

SCHEDULE_NAME The SCHEDULE_NAME parameter specifies the name of the schedule. Because schedules are stored like any other database object, standard Oracle object naming requirements are enforced for schedules. This means that the program name must not only be a valid Oracle object name, it must also be unique within the schema.

START_DATE The START_DATE parameter specifies the first date that the schedule is valid. The START_DATE parameter is used as a reference date when the REPEAT_INTERVAL parameter uses a calendaring expression. In this situation, the job runs on the first date that matches the calendaring expression and is on or after the date specified in the START_DATE parameter.

END_DATE The END_DATE parameter specifies the date when the schedule will expire. After the date specified, the job will no longer be executed; the STATE of the job will be set to COMPLETED, and the ENABLED flag will be set to FALSE.

If this parameter is set to NULL, the job will repeat forever. However, if the MAX_RUNS or MAX_FAILURES parameter is set, the job will stop if either of these thresholds is met.

REPEAT_INTERVAL The REPEAT_INTERVAL parameter specifies how often the schedule should be repeated. This parameter can be specified using either a calendaring or a PL/SQL expression. If this parameter is NULL, the job will run only once (at the scheduled start time).

COMMENTS The COMMENTS parameter allows the entry of a comment to document the schedule.

12.7.2. Creating Schedules

Schedules are created using the DBMS_SCHEDULER.CREATE_SCHEDULE procedure. By default, schedules are created with access to the PUBLIC role. Therefore, no privileges need to be granted to allow other users to use the schedule.

To create a schedule that repeats every night at 8:00 P.M., use the following example:

SQL> begin
  2  dbms_scheduler.create_schedule(
  3  schedule_name => 'NIGHTLY_8_SCHEDULE',
  4  start_date => SYSTIMESTAMP,

5  repeat_interval => 'FREQ=DAILY; BYHOUR=20',
  6  comments => 'Runs nightly at 8:00 PM'),
  7  end;

SQL> /

PL/SQL procedure successfully completed.

12.7.3. Setting Repeat Intervals

Oracle's new calendaring syntax offers tremendous flexibility when it comes to defining repeat intervals. However, the syntax does require a certain amount of getting used to. The syntax offers a set of elements that offer different methods of specifying repeating dates. By mixing and matching these elements, you can generate fairly complex repeat intervals. Table 12.3 describes the clauses and describes their usage.

Table 12.3. Calendaring Syntax Element Descriptions
NameDescription
FREQThe FREQ parameter defines the frequency type. This parameter is required. The following values are valid: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
INTERVALThe INTERVAL element specifies how often the recurrence repeats. For example, if the FREQ is set to DAILY, then an INTERVAL of 1 (the default value) means that the job will execute every day. A value of 2 means that the job would execute every other day, and so on. The maximum value is 999.
BYMONTHThe BYMONTH element specifies the month or months in which you want the job to execute. The months can be represented numerically (1–12) or using three-letter abbreviations (JAN–DEC). Multiple months should be separated by commas.
BYWEEKNOThe BYWEEKNO element specifies the week of the year as a number. It follows the ISO-8601 standard, which defines the week as starting with Monday and ending with Sunday. It also defines the first week of a year as the first week in which the majority of days fall within the Gregorian year.
BYYEARDAYThe BYYEARDAY element specifies the day of the year as a number. Positive numbers that are greater than 59 will be affected by leap day. For example, 60 would evaluate to March 1 on non-leap years, but would evaluate to February 29 on leap years. Instead, negative numbers can be used. For example, −7 will always evaluate to December 25.
BYMONTHDAYThe BYMONTHDAY element specifies the day of the month as a number. Negative numbers can be used to count backward. For example, −1 will always evaluate to the last day of the month.
BYDAYThe BYDAY element specifies the day of the week using a three-letter abbreviation (MON, TUE, and so on). Monday is always the first day of the week. You can also prepend the BYDAY element with a number representing the occurrence of the specified day. For example, if FREQ is set to MONTHLY, you can specify the last Friday of the month by using −1FRI.
BYHOURThe BYHOUR element specifies the hour on which the job is to run. Valid values are 0–23.
BYMINUTEThe BYMINUTE element specifies the minute on which the job is to run. Valid values are 0–59.
BYSECONDThe BYSECOND element specifies the second on which the job is to run. Valid values are 0–59.

Keep in mind that certain rules apply when using the calendaring syntax. These rules will aid you in creating accurate schedules:

  • The first element defined must always be the frequency. All other elements are optional and can appear in any order.

  • Elements should be separated by a semi-colon, and each element can be represented no more than once.

  • Lists of values within an element should be separated by commas. They do not need to be ordered.

  • Calendaring statements are case-insensitive, and white space is allowed between elements.

  • The BYWEEKNO element can be used only when the FREQ element is set to YEARLY. By default, it returns all days in the week, so a BYDAY setting would be required to limit the days.

  • Negative numbers are allowed with certain BY elements. For example, months have different numbers of days, so defining the last day of every month is not possible by using a single, positive number. Instead, you can specify BYMONTHDAY=-1, which will always return the last day of the month. Fixed-size elements such as BYMONTH, BYHOUR, and so on, do not support negative numbers.

  • The BYDAY element generally specifies the day of the week. However, when used in conjunction with a frequency of YEARLY or MONTHLY, you can add a positive or negative number in front of the day to achieve greater specificity. For example, a FREQ value set to MONTHLY and a BYDAY value set to -1SAT would specify the last Saturday of every month.

  • The calendaring syntax always considers Monday the first day of the week.

  • The calendaring syntax does not allow you to specify time zones or Daylight Savings Time adjustments. Instead, the region defined in the schedule's START_DATE attribute is used to determine the time zone/Daylight Savings Time adjustments.

To help you get more familiar with the calendaring syntax, examples will be provided that demonstrate different repeat intervals and the syntax used to achieve it. Table 12.4 offers a variety of examples.

Table 12.4. Calendaring Syntax Examples
GoalExpression
Every MondayFREQ=WEEKLY; BYDAY=MON;
Every other MondayFREQ=WEEKLY; BYDAY=MON; INTERVAL=2;
Last day of each monthFREQ=MONTHLY; BYMONTHDAY=-1;
Every January 7FREQ=YEARLY; BYMONTH=JAN; BYMONTHDAY=7;
Second Wednesday of each monthFREQ=MONTHLY; BYDAY=2WED;
Every hourFREQ=HOURLY;
Every 4 hoursFREQ=HOURLY; INTERVAL=4;
Hourly on the first day of each monthFREQ=HOURLY; BYMONTHDAY=1;
15th day of every other monthFREQ=MONTHLY; BYMONTHDAY=15; INTERVAL=2

12.7.4. Testing Repeat Intervals

One issue inherent in defining schedule repeat intervals is testing. How do you make sure you didn't make a mistake in your logic? To address that issue, Oracle offers the DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING procedure. This procedure allows you to pass in a calendaring syntax expression and a start date, and it will return the time and date that the job will execute next. Optionally, you can also instruct the procedure to show the next execution time after a certain date, thereby allowing you to see execution dates in the future. Table 12.5 lists the parameters for the EVALUATE_CALENDAR_STRING procedure and describes their usage.

Table 12.5. EVALUATE_CALENDAR_STRING Procedure Parameters
ParameterDescription
CALENDAR_STRINGThe calendar expression to be evaluated.
START_DATEThe date after which the repeat interval becomes valid.
RETURN_DATE_AFTERInstructs the procedure to return only execution dates that will occur after the date specified in this parameter. This allows you to see dates and times far out into the future. By default, Oracle uses the current SYSTIMESTAMP.
NEXT_RUN_DATEThis is an out parameter (the procedure will return this value to the calling program) of type TIMESTAMP that shows the date and time of the next execution.

To use the EVALUATE_CALENDAR_STRING procedure, you will need to use PL/SQL that accepts a return value of type TIMESTAMP, as shown here:

SQL> DECLARE
  2  start_date TIMESTAMP;
  3  return_date_after TIMESTAMP;
  4  next_run_date TIMESTAMP;
  5  BEGIN
  6  start_date := to_timestamp_tz(
  7  '10-OCT-2004 10:00:00','DD-MON-YYYY HH24:MI:SS'),
  8  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
  9  'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15',
 10  start_date, null, next_run_date);
 11  DBMS_OUTPUT.PUT_LINE('next_run_date: ' ||
 12  next_run_date);
 13  END;
SQL> /
next_run_date: 15-OCT-04 10.00.00.000000 AM

PL/SQL procedure successfully completed.

As you can see, line 9 contains the actual calendar expression that is being evaluated. Also, because a value of NULL was submitted for the RETURN_DATE_AFTER parameter, Oracle uses the current date and time as the default.

The procedure returns only a single value for NEXT_RUN_DATE, but you may want to see more than one. If so, you can use the SQL shown here:

SQL> DECLARE
  2  start_date TIMESTAMP;
  3  return_date_after TIMESTAMP;
  4  next_run_date TIMESTAMP;
  5  BEGIN
  6  start_date := to_timestamp_tz(
  7  '10-OCT-2004 10:00:00','DD-MON-YYYY HH24:MI:SS')
  8  return_date_after := start_date;
  9  FOR i IN 1..10 LOOP
 10  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(
 11  'FREQ=MONTHLY; INTERVAL=2; BYMONTHDAY=15',
 12  start_date, return_date_after, next_run_date);
 13  DBMS_OUTPUT.PUT_LINE(
 14  'next_run_date: ' || next_run_date);
 15  return_date_after := next_run_date;
 16  END LOOP;
 17  END;
SQL> /
next_run_date: 15-OCT-04 10.00.00.000000 AM
next_run_date: 15-DEC-04 10.00.00.000000 AM
next_run_date: 15-FEB-05 10.00.00.000000 AM
next_run_date: 15-APR-05 10.00.00.000000 AM
next_run_date: 15-JUN-05 10.00.00.000000 AM
next_run_date: 15-AUG-05 10.00.00.000000 AM
next_run_date: 15-OCT-05 10.00.00.000000 AM
next_run_date: 15-DEC-05 10.00.00.000000 AM
next_run_date: 15-FEB-06 10.00.00.000000 AM
next_run_date: 15-APR-06 10.00.00.000000 AM

PL/SQL procedure successfully completed.

This example calls the procedure inside of a loop, and each time through, it uses the NEXT_RUN_DATE returned from the prior call as the value for the RETURN_DATE_AFTER parameter. This tells Oracle to only return a date that is farther in the future than the date specified. Therefore, you will get each successive execution date.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset