114 DBMS_SCHEDULER

Data Structures

The DBMS_SCHEDULER package defines OBJECT types and TABLE types.

OBJECT Types

• JOBARG Object Type

• JOB Object Type

• JOBATTR Object Type

• SCHEDULER$_STEP_TYPE Object Type

TABLE Types

• JOBARG_ARRAY Table Type

• JOB_ARRAY Table Type

• JOBATTR_ARRAY Table Type

• SCHEDULER$_STEP_TYPE_LIST Table Type

JOBARG Object Type

This type is used by the JOB and JOBATTR object types. It represents a job argument in a batch of job arguments.

Syntax

TYPE jobarg IS OBJECT (
   arg_position         NUMBER,
   arg_text_value       VARCHAR2(4000),
   arg_anydata_value    ANYDATA,
   arg_operation        VARCHAR2(5);

Attributes

JOBARG Constructor Function

This constructor function constructs a job argument. It is overloaded. The different functionality of each form of syntax is presented along with the definitions.

Syntax

Constructs a job argument with a text value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_value           IN VARCHAR2)
   RETURN SELF AS RESULT;

Constructs a job argument with an AnyData value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_value           IN ANYDATA)
   RETURN SELF AS RESULT;

Constructs a job argument with a NULL value.

constructor function jobarg (
   arg_position        IN POSITIVEN,
   arg_reset           IN BOOLEAN DEFAULT FALSE)
   RETURN SELF AS RESULT;

Parameters

JOBARG_ARRAY Table Type

Syntax

TYPE jobarg_array IS TABLE OF jobarg;

JOB Object Type

This type is used by the CREATE_JOBS procedure and represents a job in a batch of jobs.

Syntax

TYPE job IS OBJECT (
   job_name             VARCHAR2(100),
   job_class            VARCHAR2(32),
   job_style            VARCHAR2(11),
   job_template         VARCHAR2(100)
   program_action       VARCHAR2(4000),
   action_type          VARCHAR2(20),
   schedule_name        VARCHAR2(65),
   repeat_interval      VARCHAR2(4000),
   schedule_limit       INTERVAL DAY(2) TO SECOND(6),
   start_date           TIMESTAMP(6) WITH TIME ZONE,
   end_date             TIMESTAMP(6) WITH TIME ZONE,
   event_condition      VARCHAR2(4000),
   queue_spec           VARCHAR2(100),
   number_of_args       NUMBER,
   arguments            JOBARG_ARRAY,
   priority             NUMBER,
   job_weight           NUMBER,
   max_run_duration     INTERVAL DAY(2) TO SECOND(6),
   max_runs             NUMBER,
   max_failures         NUMBER,
   logging_level        NUMBER,
   restartable          VARCHAR2(5),
   stop_on_window_exit  VARCHAR2(5),
   raise_events         NUMBER,
   comments             VARCHAR2(240),
   auto_drop            VARCHAR2(5),
   enabled              VARCHAR2(5),
   follow_default_tz    VARCHAR2(5),
   parallel_instances   VARCHAR2(5),
   aq_job               VARCHAR2(5),
   instance_id          NUMBER);

Object Attributes

Table Table 114-3 describes the attributes of the JOB object type. For more information about these attributes, see "SET_ATTRIBUTE Procedure".

JOB Constructor Function

This constructor function constructs a job object.

Syntax

constructor function job (
   job_name            IN VARCHAR2,
   job_style           IN VARCHAR2 DEFAULT 'REGULAR',
   job_template        IN VARCHAR2 DEFAULT NULL,
   program_action      IN VARCHAR2 DEFAULT NULL,
   action_type         IN VARCHAR2 DEFAULT NULL,
   schedule_name       IN VARCHAR2 DEFAULT NULL,
   repeat_interval     IN VARCHAR2 DEFAULT NULL,
   event_condition     IN VARCHAR2 DEFAULT NULL,
   queue_spec          IN VARCHAR2 DEFAULT NULL,
   start_date          IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   end_date            IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   number_of_args      IN NATURAL DEFAULT NULL,
   arguments           IN JOBARG_ARRAY DEFAULT NULL,
   job_class           IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS',
   schedule_limit      IN INTERVAL DAY TO SECOND DEFAULT NULL,
   priority            IN NATURAL DEFAULT NULL,
   job_weight          IN NATURAL DEFAULT NULL,
   max_run_duration    IN INTERVAL DAY TO SECOND DEFAULT NULL,
   max_runs            IN NATURAL DEFAULT NULL,
   max_failures        IN NATURAL DEFAULT NULL,
   logging_level       IN NATURALN DEFAULT 64,
   restartable         IN BOOLEAN DEFAULT FALSE,
   stop_on_window_exit IN BOOLEAN DEFAULT FALSE,
   raise_events        IN NATURAL DEFAULT NULL,
   comments            IN VARCHAR2 DEFAULT NULL,
   auto_drop           IN BOOLEAN DEFAULT TRUE,
   enabled             IN BOOLEAN DEFAULT FALSE,
   follow_default_tz   IN BOOLEAN DEFAULT FALSE,
   parallel_instances  IN BOOLEAN DEFAULT FALSE,
   aq_job              IN BOOLEAN DEFAULT FALSE,
   instance_id         IN NATURAL DEFAULT NULL)
   RETURN SELF AS RESULT;

JOB_ARRAY Table Type

Syntax

TYPE job_array IS TABLE OF job;

JOBATTR Object Type

This type is used by the SET_JOB_ATTRIBUTES procedure and represents a job attribute in a batch of job attributes.

Syntax

TYPE jobattr IS OBJECT (
   job_name             VARCHAR2(100),
   attr_name            VARCHAR2(30),
   char_value           VARCHAR2(4000),
   char_value2          VARCHAR2(4000),
   args_value           JOBARG_ARRAY,
   num_value            NUMBER,
   timestamp_value      TIMESTAMP(6) WITH TIME ZONE,
   interval_value       INTERVAL DAY(2) TO SECOND(6);

Attributes

JOBATTR Constructor Function

This constructor function constructs a job attribute. It is overloaded to create attribute values of the following types: VARCHAR2, NUMBER, TIMESTAMP WITH TIME ZONE, INTERVAL DAY TO SECOND, and an array of JOBARG types.

Syntax

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2,
   attr_value          IN VARCHAR2,
   attr_value2         IN VARCHAR2 DEFAULT NULL)
   RETURN SELF AS RESULT;

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2,
   attr_value          IN [NUMBER, BOOLEAN,
                           TIMESTAMP WITH TIME ZONE,
                           INTERVAL DAY TO SECOND, JOBARG_ARRAY])
   RETURN SELF AS RESULT;

constructor function jobattr (
   job_name            IN VARCHAR2,
   attr_name           IN VARCHAR2)
   RETURN SELF AS RESULT;

Parameters

JOBATTR_ARRAY Table Type

Syntax

TYPE jobattr_array IS TABLE OF jobattr;

SCHEDULER$_STEP_TYPE Object Type

This type is used by RUN_CHAIN to return a List of chain steps with an initial state.

Syntax

TYPE scheduler$_step_type IS OBJECT (
   step_name  VARCHAR2(32),
   step_type  VARCHAR2(32));

Attributes

SCHEDULER$_STEP_TYPE_LIST Table Type

Syntax

TYPE scheduler$_step_type_list IS TABLE OF scheduler$_step_type;

Using DBMS_SCHEDULER

This section contains topics which relate to using the DBMS_SCHEDULER package.

• Rules and Limits

• Operational Notes

Rules and Limits

The following rules apply when using the DBMS_SCHEDULER package:

• Only SYS can do anything in SYS schema.

• Several of the procedures accept comma-delimited lists of object names. When a list of names is provided, the Scheduler will stop executing the list on the very first object that returns an error. This means that the Scheduler will not perform the task on the objects in the list after the one that caused the error. For example, consider the statement DBMS_SCHEDULER.STOP_JOB ('job1, job2, job3, sys.jobclass1, sys.jobclass2, sys.jobclass3'); If job3 could not be stopped, then job1 and job2 will be stopped, but the jobs in jobclass1, jobclass2, and jobclass3 will not be stopped.

• Performing an action on an object that does not exist returns a PL/SQL exception stating that the object does not exist.

Operational Notes

The Scheduler uses a rich calendaring syntax to enable you to define repeating schedules, such as "every Tuesday and Friday at 4:00 p.m." or "the second Wednesday of every month." This calendaring syntax is used in calendaring expressions in the repeat_interval argument of a number of package subprograms. Evaluating a calendaring expression results in a set of discrete timestamps.

See Oracle Database Administrator's Guide for examples of the calendaring syntax.

Calendaring Syntax

The calendaring syntax is as follows:

repeat_interval = regular_schedule | combined_schedule
 
regular_schedule = frequency_clause
[";" interval_clause] [";" bymonth_clause] [";" byweekno_clause]
[";" byyearday_clause] [";" bydate_clause] [";" bymonthday_clause]
[";" byday_clause] [";" byhour_clause] [";" byminute_clause]
[";" bysecond_clause] [";" bysetpos_clause] [";" include_clause]
[";" exclude_clause] [";" intersect_clause][";" periods_clause]
[";" byperiod_clause]
 
combined_schedule = schedule_list [";" include_clause]
[";" exclude_clause] [";" intersect_clause]

frequency_clause = "FREQ" "=" ( predefined_frequency | user_defined_frequency )
predefined_frequency = "YEARLY" | "MONTHLY" | "WEEKLY" | "DAILY" | 
   "HOURLY" | "MINUTELY" | "SECONDLY"
user_defined_frequency = named_schedule

interval_clause = "INTERVAL" "=" intervalnum
   intervalnum = 1 through 99
bymonth_clause = "BYMONTH" "=" monthlist
   monthlist = monthday ( "," monthday)*
   month = numeric_month | char_month
   numeric_month = 1 | 2 | 3 ...  12
   char_month = "JAN" | "FEB" | "MAR" | "APR" | "MAY" | "JUN" |
   "JUL" | "AUG" | "SEP" | "OCT" | "NOV" | "DEC"
byweekno_clause = "BYWEEKNO" "=" weeknumber_list
   weeknumber_list = weeknumber ( "," weeknumber)*
   weeknumber = [minus] weekno
   weekno = 1 through 53
byyearday_clause = "BYYEARDAY" "=" yearday_list
   yearday_list = yearday ( "," yearday)*
   yearday = [minus] yeardaynum
   yeardaynum = 1 through 366
bydate_clause = "BYDATE" "=" date_list
   date_list = date ( "," date)*
   date = [YYYY]MMDD [ offset | span ]
bymonthday_clause = "BYMONTHDAY" "=" monthday_list
   monthday_list = monthday ( "," monthday)*
   monthday = [minus] monthdaynum
   monthdaynum = 1 through 31
byday_clause = "BYDAY" "=" byday_list
   byday_list = byday ( "," byday)*
   byday = [weekdaynum] day
   weekdaynum = [minus] daynum
   daynum = 1 through 53 /* if frequency is yearly */
   daynum = 1 through 5  /* if frequency is monthly */
   day = "MON" | "TUE" | "WED" | "THU" | "FRI" | "SAT" | "SUN"
byhour_clause = "BYHOUR" "=" hour_list
   hour_list = hour ( "," hour)*
   hour = 0 through 23
byminute_clause = "BYMINUTE" "=" minute_list
   minute_list = minute ( "," minute)*
   minute = 0 through 59
bysecond_clause = "BYSECOND" "=" second_list
   second_list = second ( "," second)*
   second = 0 through 59
bysetpos_clause = "BYSETPOS" "=" setpos_list
   setpos_list = setpos ("," setpos)*
   setpos = [minus] setpos_num
   setpos_num = 1 through 9999

include_clause = "INCLUDE" "=" schedule_list
exclude_clause = "EXCLUDE" "=" schedule_list
intersect_clause = "INTERSECT" "=" schedule_list
schedule_list = schedule_clause ("," schedule_clause)*
schedule_clause = named_schedule [ offset ]
named_schedule = [schema "."] schedule
periods_clause = "PERIODS" "=" periodnum
byperiod_clause = "BYPERIOD" "=" period_list
period_list = periodnum ("," periodnum)*
periodnum = 1 through 100

offset = ("+" | "-") ["OFFSET:"] duration_val
span = ("+" | "-" | "^") "SPAN:" duration_val
duration_val = dur-weeks | dur_days
dur_weeks = numofweeks "W"
dur_days = numofdays "D"
numofweeks = 1 through 53
numofdays = 1 through 376
minus = "-"

In calendaring syntax, * means 0 or more.

FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1

3/31/2005, 6/30/2005, 9/30/2005, 12/31/2005

3/31/2005, 4/30/2005, 6/30/2005, 9/30/2005, 12/31/2005

FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=last_sat,end_qtr

FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4

Combining Schedules

There are two ways to combine schedules:

• Using a combined schedule expression, which is a list of individual schedules

  For example, to create a schedule for all company holidays, you provide a list of individual schedules, where each schedule in the list defines a single holiday. The Scheduler evaluates each individual schedule, and then returns a union of the timestamps returned by each individual schedule. You can follow the initial list of individual schedules with include, exclude, and intersect clauses to create more complex combinations.

• Embedding other schedules into the main schedule using include, exclude, and intersect clauses

  With this method, the embedded schedules inherit certain attributes from the main schedule.

  • Timestamps generated by the INCLUDE clause that fall into periods that are skipped by the main schedule are ignored. This is the case when the main schedule skips periods due to the INTERVAL clause, the BYPERIOD clause, or the BYMONTH clause for freq=monthly.

  • Days that are added by the INCLUDE clause follow the hourly/minutely/secondly execution pattern of the main schedule.

  • When the INCLUDE clause is present, no date-specific defaults are retrieved from the start date (but time-specific defaults can be). (See "Start Dates and Repeat Intervals", later in this section.) For example, a repeat_interval of FREQ=MONTHLY;INCLUDE=HOLIDAY executes only on holidays and not on the month/day defaults retrieved from the start date.

  The following is an example:

  BEGIN
  dbms_scheduler.create_schedule('embed_sched', repeat_interval =>
    'FREQ=YEARLY;BYDATE=0130,0220,0725');
  dbms_scheduler.create_schedule('main_sched', repeat_interval =>
    'FREQ=MONTHLY;INTERVAL=2;BYMONTHDAY=15;BYHOUR=9,17;INCLUDE=embed_sched');
  END;
  /
  
  

  In this example, the dates 1/30, 2/20, and 7/25 are added to the main schedule. However, the Scheduler does not include dates that fall in months that are skipped by the INTERVAL clause. If the start date of the main schedule is 1/1/2005, then 2/20 isn't added. On the dates that are added, the embedded schedule follows the execution pattern of the main schedule: jobs are executed at 9:00 a.m. and 5:00 p.m. on 1/30 and 7/25. If the embedded schedule does not itself have a start date, it inherits the start date from the main schedule.

User Defined Frequencies

Instead of using predefined frequencies like DAILY, WEEKLY, MONTHLY, and so on, you can create your own frequencies by creating a schedule that returns the start date of each period. For example, the following repeat_interval expression is used in a schedule named fiscal_year that defines the start of each quarter in a fiscal year:

FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4

To return the last Wednesday of every quarter, you create a schedule (the "main schedule") that uses the fiscal_year schedule as a user defined frequency:

FREQ=fiscal_year;BYDAY=-1WED

Periods in a user defined frequency do not have to be equal in length. In the main schedule, the BYSETPOS clause and numbered weekdays are recalculated based on the size of each period. To select dates in specific periods, you must use the BYPERIOD clause in the main schedule. To enable this, the schedule that is used as the user defined frequency must include a PERIODS clause, and it must set its start date appropriately. The first date returned by this schedule is used as the starting point of period 1.

As another example, assuming work days are Monday through Friday, to get the last work day of the 2nd and 4th quarters of the fiscal year, the repeat_interval clause in the main schedule is the following:

FREQ=fiscal_year;BYDAY=MON,TUE,WED,THU,FRI;BYPERIOD=2,4;BYSETPOS=-1

Start Dates and Repeat Intervals

The Scheduler retrieves the date and time from the job or schedule start date and incorporates them as defaults into the repeat_interval. For example, if the specified frequency is yearly and there is no BYMONTH or BYMONTHDAY clause in the repeat interval, the month and day on which to run the job are retrieved from the start date. Similarly, if frequency is monthly but there is no BYMONTHDAY clause in the repeat interval, the day of the month on which to run the job is retrieved from the start date. If present, BYHOUR, BYMINUTE, and BYSECOND defaults are also retrieved from the start date, and used if those clauses are not specified. Note that if the INCLUDE, EXCLUDE, or INTERSECT clauses are present, no date-related defaults are retrieved from the start date, but time-related defaults are.The following are some examples:

start_date:      4/15/05 9:00:00
repeat_interval: freq=yearly

is expanded internally to:

freq=yearly;bymonth=4;bymonthday=15;byhour=9;byminute=0;bysecond=0

The preceding schedule executes on 04/15/05 9:00:00, 04/15/06 9:00:00, 04/15/07 9:00:00, and so on.

For the next example, assume that schedule S1 has a repeat_interval of FREQ=YEARLY;BYDATE=0701.

start_date:      01/20/05 9:00:00
repeat_interval: freq=yearly;include=S1

is expanded internally to:

freq=yearly;byhour=9;byminute=0;bysecond=0;include=S1

Because an INCLUDE clause is present, date-related information is not retrieved from the start date. However, time-specific information is, so the preceding schedule executes on 07/01/05 9:00:00, 07/01/06 9:00:00, 07/01/08 9:00:00, and so on.

General Rules

When using a calendaring expression, consider the following rules:

• For a regular schedule (as opposed to a combined schedule), the calendar string must start with the frequency clause. All other clauses are optional and can be put in any order.

• All clauses are separated by a semi-colon, and each clause can be present at most once, with the exception of the include, exclude, and intersect clauses.

• Spaces are allowed between syntax elements and the strings are case insensitive.

• The list of values for a specific BY clause do not need to be ordered.

• When not enough BY clauses are present to determine what the next date is, this information is retrieved from the start date. For example, "FREQ=YEARLY" with a start date of 02/15/2003 becomes "FREQ=YEARLY;BYMONTH=FEB; BYMONTHDAY=15", which means every year on the 15th of February.

  "FREQ=YEARLY;BYMONTH=JAN,JUL" with start date 01/21/2003 becomes "FREQ=YEARLY;BYMONTH=JAN,JUL;BYMONTHDAY=21", which means every year on January 21 and July 21.

• The byweekno clause is only allowed if the frequency is YEARLY. It cannot be used with other frequencies. When it is present, it will return all days in that week number. If you want to limit it to specific days within the week, you have to add a BYDAY clause. For example, "FREQ=YEARLY;BYWEEKNO=2" with a start date of 01/01/2003 will return:

  01/06/2003, 01/07/2003, 01/08/2003, 01/09/2003, 01/10/2003, 01/11/2003, 01/12/2003, 01/05/2004, 01/06/2004, 01/07/2004, .... and so on.
  
  

  Note that when the byweekno clause is used, it is possible that the dates returned are from a year other than the current year. For example, if returning dates for the year 2004 and the calendar string is "FREQ=YEARLY;BYWEEKNO=1,53" for the specified week numbers in 2004, it will return the dates:

  12/29/03, 12/30/03, 12/31/03, 01/01/04, 01/02/04, 01/03/04, 01/04/04, 12/27/04, 12/28/04, 12/29/04, 12/30/04, 12/31/04, 01/01/05, 01/02/05
  
  

• For those BY clauses that do not have a consistent range of values, you can count backward by putting a "-" in front of the numeric value. For example, specifying BYMONTHDAY=31 will not give you the last day of every month, because not every month has 31 days. Instead, BYMONTHDAY=-1 will give you the last day of the month.

  This is not supported for BY clauses that are fixed in size. In other words, BYMONTH, BYHOUR, BYMINUTE, and BYSECOND are not supported.

• The basic values for the BYDAY clause are the days of the week. When the frequency is YEARLY, or MONTHLY, you are allowed to specify a positive or negative number in front of each day of the week. In the case of YEARLY, BYDAY=40MON, indicates the 40th Monday of the year. In the case of MONTHLY, BYDAY=-2SAT, indicates the second to last Saturday of the month.

  Note that positive or negative numbers in front of the weekdays are not supported for other frequencies and that in the case of yearly, the number ranges from -53 ... -1, 1 ... 53, whereas for the monthly frequency it is limited to -5 ... -1, 1... 5.

  If no number is present in front of the weekday it specifies, every occurrence of that weekday in the specified frequency.

• The first day of the week is Monday.

• Repeating jobs with frequencies smaller than daily follow their frequencies exactly across daylight savings adjustments. For example, suppose that a job is scheduled to repeat every 3 hours, the clock is moved forward from 1:00 a.m. to 2:00 a.m., and the last time the job ran was midnight. Its next scheduled time will be 4:00 a.m. Thus, the 3 hour period between subsequent job runs is retained. The same applies when the clock is moved back. This behavior is not the case for repeating jobs that have frequencies of daily or larger. For example, if a repeating job is supposed to be executed on a daily basis at midnight, it will continue to run at midnight if the clock is moved forward or backward. When the execution time of such a daily (or larger frequency) job happens to fall inside a window where the clock is moved forward, the job executes at the end of the window.

• The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments you must make sure that you specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York will make sure that daylight saving adjustments are automatically applied. If instead the time zone of the start_date is set to an absolute offset, such as '-5:00', daylight savings adjustments are not followed and your job execution will be off by an hour half of the year.

• When start_date is NULL, the Scheduler will determine the time zone for the repeat interval as follows:

  1. It will check whether the session time zone is a region name. The session time zone can be set by either:

     • Issuing an ALTER SESSION statement, for example:

       SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
       
       

     • Setting the ORA_SDTZ environment variable.

  2. If the session time zone is an absolute offset instead of a region name, the Scheduler will use the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

  3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler will use the time zone of systimestamp when the job or window is enabled.

BYSETPOS Clause Rules

• The BYSETPOS clause is the last clause to be evaluated. It is processed after all other BY clauses and the INCLUDE, EXCLUDE and INTERSECT clauses have been evaluated.

• The INTERVAL clause does not change the size of the period to which the BYSETPOS clause is applied. For example, when the frequency is set to monthly and interval is set to 3, the list of timestamps to which BYSETPOS is applied is generated from a month, not a quarter. The only impact of the INTERVAL clause is to cause months to be skipped. However, you can still select the second to last workday of the quarter like this:

  FREQ=MONTHLY;INTERVAL=3;BYDAY=MON,TUE,WED,THU,FRI;BYSETPOS=-2
  
  

  provided that you set the start date in the right month. This example returns the next to last workday of a month, and repeats once a quarter.

• To get consistent results, the set to which BYSETPOS is applied is determined from the beginning of the frequency period independently of when the evaluation occurs. Whether the Scheduler evaluates

  FREQ=MONTHLY;BYDAY=MON,TUE,FRI;BYSETPOS=1,3
  
  

  on 01/01/2004 or 01/15/2004, in both cases the expression evaluates to Friday 01/02/2004, and Tuesday 01/06/2004. The only difference is that when the expression is evaluated on 01/15/2004, the Scheduler determines that there are no matches in January because the timestamps found are in the past, and it moves on to the matches in the next month, February.

BYDATE Clause Rules

• If dates in the BYDATE clause do not have their optional year component, the job runs on those dates every year.

• The job execution times on the included dates are derived from the BY clauses in the calendaring expression. For example, if repeat_interval is defined as

  freq=daily;byhour=8,13,18;byminute=0;bysecond=0;bydate=0502,0922
  
  

  then the execution times on 05/02 and 09/22 are 8:00 a.m., 1:00 p.m., and 6:00 p.m.

EXCLUDE Clause Rules

• Excluded dates without a time component are 24 hour periods. All timestamps that fall on an excluded date are removed. In the following example, jan_fifteen is a named schedule that resolves to the single date of 01/15:

  freq=monthly;bymonthday=15,30;byhour=8,13,18;byminute=0;bysecond=0;
       exclude=jan_fifteenth
  
  

  In this case, all three instances of the job are removed for 01/15.

OFFSET Rules

• You can adjust the dates of individual named schedules by adding positive offsets to them. For example, to execute JOB2 exactly 15 days after every occurrence of JOB1, add +OFFSET:15D to the schedule of JOB1, as follows:

  BEGIN
  dbms_scheduler.create_schedule('job2_schedule', repeat_interval =>
    'job1_schedule+OFFSET:15D');
  END;
  /
  
  

  Note that negative offsets to named schedules are not supported.

Example: Putting It All Together

This example demonstrates the use of user defined frequencies, spans, offsets, and the BYSETPOS and INCLUDE clauses. (Note that the OFFSET: keyword in an offset clause is optional.)

Many companies in the retail industry share the same fiscal year. The fiscal year starts on the Sunday closest to February 1st, and subsequent quarters start exactly 13 weeks later. The fiscal year schedule for the retail industry can be defined as the following:

begin
 dbms_scheduler.create_schedule('year_start', repeat_interval=>
       'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');
 dbms_scheduler.create_schedule('retail_fiscal_year',
        to_timestamp_tz('15-JAN-2005 12:00:00','DD-MON-YYYY HH24:MI:SS'),
         'year_start,year_start+13w,year_start+26w,year_start+39w;periods=4');
end;
/

The following schedule can be used to execute a job on the 5th day off in the 2nd and the 4th quarters of the retail industry. This assumes that Saturday and Sunday are off days as well as the days in the existing holiday schedule.

begin
 dbms_scheduler.create_schedule('fifth_day_off', repeat_interval=>
  'FREQ=retail_fiscal_year;BYDAY=SAT,SUN;INCLUDE=holiday;
    BYPERIOD=2,4;BYSETPOS=5');
end;
/

Summary of DBMS_SCHEDULER Subprograms

ADD_EVENT_QUEUE_SUBSCRIBER Procedure

This procedure adds a user as a subscriber to the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE, and grants the user permission to dequeue from this queue using the designated agent.

Syntax

DBMS_SCHEDULER.ADD_EVENT_QUEUE_SUBSCRIBER (
   subscriber_name         IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

The subscription is rule-based. The rule permits the user to see only events raised by jobs that the user owns, and filters out all other messages. If an AQ agent with the same name already exists, an error is raised.

ADD_WINDOW_GROUP_MEMBER Procedure

This procedure adds one or more windows to an existing window group.

Syntax

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name              IN VARCHAR2,
   window_list             IN VARCHAR2);

Parameters

Usage Notes

If an already open window is added to a window group, the Scheduler will not pick up jobs that point to this window group until the next window in the window group opens.

Adding a window to a group requires the MANAGE SCHEDULER privilege.

Note that a window group cannot be a member of another window group.

ALTER_CHAIN Procedure

This procedure alters specified steps of a chain. This affects all future runs of the specified steps.

Syntax

DBMS_SCHEDULER.ALTER_CHAIN (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   value                   IN BOOLEAN);

Parameters

Usage Notes

Altering a chain requires ALTER privileges on the chain either by being the owner of the chain, or by having the ALTER object privilege on the chain or by having the CREATE ANY JOB system privilege.

ALTER_RUNNING_CHAIN Procedure

This procedure alters specified steps of a running chain. This will affect only steps of this running instance of the chain.

Syntax

DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (
   job_name                IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   value                   IN [BOOLEAN|VARCHAR2]);

Parameters

Usage Notes

Altering a running chain requires alter privileges on the job which is running (either by being the owner, or by having ALTER privileges on the job or by having the CREATE ANY JOB system privilege).

CLOSE_WINDOW Procedure

This procedure closes an open window prematurely. A closed window means that it is no longer in effect. When a window is closed, the Scheduler will switch the resource plan to the one that was in effect outside the window or in the case of overlapping windows to another window.

Syntax

DBMS_SCHEDULER.CLOSE_WINDOW (
   window_name             IN VARCHAR2);

Parameters

Usage Notes

If you try to close a window that does not exist or is not open, an error is generated.

A job that is running will not stop when the window it is running in closes unless the attribute stop_on_window_close was set to TRUE for the job. However, the resources allocated to the job may change because the resource plan may change.

When a running job has a window group as its schedule, the job will not be stopped when its window is closed if another window that is also a member of the same window group then becomes active. This is the case even if the job has the attribute stop_on_window_close set to TRUE.

Closing a window requires the MANAGE SCHEDULER privilege.

COPY_JOB Procedure

This procedure copies all attributes of an existing job to a new job. The new job is created disabled, while the state of the existing job is unaltered.

Syntax

DBMS_SCHEDULER.COPY_JOB (
   old_job                IN VARCHAR2,
   new_job                IN VARCHAR2);

Parameters

Usage Notes

Copying a job requires privileges to create a job in the schema of the new job (the CREATE JOB system privilege if it is in the user's own schema, and the CREATE ANY JOB system privilege otherwise). If the old job is not in the user's own schema, then he needs to additionally have ALTER privileges on the old job or the CREATE ANY JOB system privilege.

CREATE_CHAIN Procedure

This procedure creates a new chain. The chain name can be optionally qualified with a schema name (for example, myschema.myname).

A chain is always created disabled and must be enabled with the ENABLE Procedure before it can be used.

Syntax

DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name              IN VARCHAR2,
   rule_set_name           IN VARCHAR2 DEFAULT NULL,
   evaluation_interval     IN INTERVAL DAY TO SECOND DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Creating a chain in one's own schema requires the CREATE JOB system privilege. Creating a chain in a different schema requires the CREATE ANY JOB system privilege. If no rule_set_name is given, a rule set and evaluation context will be created in the schema that the chain is being created in, so the user will need to have the privileges required to create these objects. See the DBMS_RULE_ADM.CREATE_RULE_SET and DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT procedures for more information.

CREATE_CREDENTIAL Procedure

This procedure creates a stored username/password pair in a database object called a credential.

Syntax

DBMS_SCHEDULER.CREATE_CREDENTIAL (
   credential_name         IN VARCHAR2,
   username                IN VARCHAR2,
   password                IN VARCHAR2,
   database_role           IN VARCHAR2 DEFAULT NULL,
   windows_domain          IN VARCHAR2 DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

Credentials reside in a particular schema and can be created by any user with the CREATE JOB system privilege. To create a credential in a schema other than your own, you must have the CREATE ANY JOB privilege.

The user name is case sensitive. It cannot contain double quotes or spaces.

CREATE_EVENT_SCHEDULE Procedure

This procedure creates an event schedule, which is used to start a job when a particular event is raised.

Syntax

DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name           IN VARCHAR2,
   start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   event_condition         IN VARCHAR2,
   queue_spec              IN VARCHAR2,
   end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Usage Notes

This procedure requires the CREATE JOB privilege to create a schedule in one's own schema or the CREATE ANY JOB privilege to create a schedule in someone else's schema by specifying schema.schedule_name. Once a schedule has been created, it can be used by other users. The schedule is created with access to PUBLIC. Therefore, there is no need to explicitly grant access to the schedule.

CREATE_JOB Procedures

This procedure creates a single job (regular or lightweight).

The procedure is overloaded. The different functionality of each form of syntax is presented along with the syntax declaration.

Syntax

Creates a job in a single call without using an existing program or schedule:

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   job_type             IN VARCHAR2,
   job_action           IN VARCHAR2,
   number_of_arguments  IN PLS_INTEGER              DEFAULT 0,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL);

Creates a job using a named schedule object and a named program object:

DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   program_name            IN VARCHAR2,
   schedule_name           IN VARCHAR2,
   job_class               IN VARCHAR2              DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN               DEFAULT FALSE,
   auto_drop               IN BOOLEAN               DEFAULT TRUE,
   comments                IN VARCHAR2              DEFAULT NULL,
   job_style               IN VARCHAR2              DEFAULT 'REGULAR');

Creates a job using a named program object and an inlined schedule:

DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   program_name         IN VARCHAR2,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL,
   job_style               IN VARCHAR2              DEFAULT 'REGULAR');

Creates a job using a named schedule object and an inlined program:

DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   schedule_name           IN VARCHAR2,
   job_type                IN VARCHAR2,
   job_action              IN VARCHAR2,
   number_of_arguments     IN PLS_INTEGER       DEFAULT 0,
   job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN           DEFAULT FALSE,
   auto_drop               IN BOOLEAN           DEFAULT TRUE,
   comments                IN VARCHAR2          DEFAULT NULL);

Creates a job using an inlined program and an event:

DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   job_type                IN VARCHAR2,
   job_action              IN VARCHAR2,
   number_of_arguments     IN PLS_INTEGER       DEFAULT 0,
   start_date              IN TIMESTAMP WITH TIME ZONE,
   event_condition         IN VARCHAR2,
   queue_spec              IN VARCHAR2,
   end_date                IN TIMESTAMP WITH TIME ZONE,
   job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN           DEFAULT FALSE,
   auto_drop               IN BOOLEAN           DEFAULT TRUE,
   comments                IN VARCHAR2          DEFAULT NULL);

Creates a job using a named program object and an event:

DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   program_name            IN VARCHAR2,
   start_date              IN TIMESTAMP WITH TIME ZONE,
   event_condition         IN VARCHAR2,
   queue_spec              IN VARCHAR2,
   end_date                IN TIMESTAMP WITH TIME ZONE,
   job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN           DEFAULT FALSE,
   auto_drop               IN BOOLEAN           DEFAULT TRUE,
   comments                IN VARCHAR2          DEFAULT NULL,
   job_style               IN VARCHAR2              DEFAULT 'REGULAR');

Parameters