93 DBMS_SCHEDULER

The DBMS_SCHEDULER package provides a collection of scheduling functions and procedures that are callable from any PL/SQL program.

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.

Table 93-1 Values for repeat_interval

Name	Description
`FREQ`	This specifies the type of recurrence. It must be specified. The possible predefined frequency values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY. Alternatively, specifies an existing schedule to use as a user-defined frequency.
`INTERVAL`	This specifies a positive integer representing how often the recurrence repeats. The default is 1, which means every second for secondly, every day for daily, and so on. The maximum value is 999.
`BYMONTH`	This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July.
`BYWEEKNO`	This specifies the week of the year as a number. It follows ISO-8601, which defines the week as starting with Monday and ending with Sunday; and the first week of a year as the first week, which is mostly within the Gregorian year. That last definition is equivalent to the following two variants: the week that contains the first Thursday of the Gregorian year; and the week containing January 4th. The ISO-8601 week numbers are integers from 1 to 52 or 53; parts of week 1 may be in the previous calendar year; parts of week 52 may be in the following calendar year; and if a year has a week 53, parts of it must be in the following calendar year. As an example, in the year 1998 the ISO week 1 began on Monday December 29th, 1997; and the last ISO week (week 53) ended on Sunday January 3rd, 1999. So December 29th, 1997, is in the ISO week 1998-01; and January 1st, 1999, is in the ISO week 1998-53. `byweekno` is only valid for YEARLY. Examples of invalid specifications are `"FREQ=YEARLY; BYWEEKNO=1; BYMONTH=12"` and `"FREQ=YEARLY;BYWEEKNO=53;BYMONTH=1"`.
`BYYEARDAY`	This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year.
`BYDATE`	This specifies a list of dates, where each date is of the form `[YYYY]MMDD`. A list of consecutive dates can be generated by using the `SPAN` modifier, and a date can be adjusted with the `OFFSET` modifier. An example of a simple `BYDATE` clause is the following: `BYDATE=0115,0315,0615,0915,1215,20060115` The following `SPAN` example is equivalent to `BYDATE=0110,0111,0112,0113,0114`, which is a span of 5 days starting at 1/10: `BYDATE=0110+SPAN:5D` The plus sign in front of the `SPAN` keyword indicates a span starting at the supplied date. The minus sign indicates a span ending at the supplied date, and the "^" sign indicates a span of n days or weeks centered around the supplied date. If n is an even number, it is adjusted up to the next odd number. Offsets adjust the supplied date by adding or subtracting n days or weeks. `BYDATE=0205-OFFSET:2W` is equivalent to `BYDATE=0205-14D` (the `OFFSET:` keyword is optional), which is also equivalent to `BYDATE=0122`.
`BYMONTHDAY`	This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, `BYMONTHDAY=-1` means the last day of the month and `BYMONTHDAY=-2` means the next to last day of the month.
`BYDAY`	This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, `-1 FRI` is the last Friday of the month.
`BYHOUR`	This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10 a.m.
`BYMINUTE`	This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour.
`BYSECOND`	This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute.
`BYSETPOS`	This selects one or more items by position in the list of timestamps that result after the whole calendaring expression is evaluated. It is useful for requirements such as running a job on the last workday of the month. Rather than attempting to express this with the other `BY` clauses, you can code the calendaring expression to evaluate to a list of every workday of the month, and then add the `BYSETPOS` clause to select only the last item of that list. Assuming that workdays are Monday through Friday, the syntax would then be: FREQ=MONTHLY; BYDAY=MON,TUE,WED,THU,FRI; BYSETPOS=-1 Valid values are 1 through 9999. A negative number selects an item from the end of the list (-1 is the last item, -2 is the next to last item, and so on) and a positive number selects from the front of the list. The `BYSETPOS` clause is always evaluated last. `BYSETPOS` is only supported with the `MONTHLY` and `YEARLY` frequencies. The `BYSETPOS` clause is applied to the list of timestamps once per frequency period. For example, when the frequency is defined as `MONTHLY`, the Scheduler determines all valid timestamps for the month, orders that list, and then applies the `BYSETPOS` clause. The Scheduler then moves on to the next month and repeats the procedure. Assuming a start date of Jun 10, 2004, the example evaluates to: Jun 30, Jul 30, Aug 31, Sep 30, Oct 29, and so on.
`INCLUDE`	This includes one or more named schedules in the calendaring expression. That is, the set of timestamps defined by each included named schedule is added to the results of the calendaring expression. If an identical timestamp is contributed by both an included schedule and the calendaring expression, it is included in the resulting set of timestamps only once. The named schedules must have been defined with the `CREATE_SCHEDULE` procedure.
`EXCLUDE`	This excludes one or more named schedules from the calendaring expression. That is, the set of timestamps defined by each excluded named schedule is removed from the results of the calendaring expression. The named schedules must have been defined with the `CREATE_SCHEDULE` procedure.
`INTERSECT`	This specifies an intersection between the calendaring expression results and the set of timestamps defined by one or more named schedules. Only the timestamps that appear both in the calendaring expression and in one of the named schedules are included in the resulting set of timestamps. For example, assume that the named schedule `last_sat` indicates the last Saturday in every month, and that for the year 2005, the only months where the last day of the month is also a Saturday are April and December. Assume also that the named schedule `end_qtr` indicates the last day of each quarter in 2005: 3/31/2005, 6/30/2005, 9/30/2005, 12/31/2005 The following calendaring expression results in the these dates: 3/31/2005, 4/30/2005, 6/30/2005, 9/30/2005, 12/31/2005 FREQ=MONTHLY; BYMONTHDAY=-1; INTERSECT=last_sat,end_qtr In this example, the terms `FREQ=MONTHLY; BYMONTHDAY=-1` indicate the last day of each month.
`PERIODS`	This identifies the number of periods that together form one cycle of a user defined frequency. It is used in the `repeat_interval` expression of the schedule that defines the user defined frequency. It is mandatory when the `repeat_interval` expression in the main schedule contains a `BYPERIOD` clause. The following example defines the quarters of a fiscal year. FREQ=YEARLY;BYDATE=0301,0601,0901,1201;PERIODS=4
`BYPERIOD`	This selects periods from a user defined frequency. For example, if a main schedule names a user defined frequency schedule that defines the fiscal quarters shown in the previous example, the clause `BYPERIOD=2,4` in the main schedule selects the 2nd and 4th fiscal quarters.

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

Table 93-2 DBMS_SCHEDULER Package Subprograms

Subprogram	Description
ADD_EVENT_QUEUE_SUBSCRIBER Procedure	Adds a user as a subscriber to the Scheduler event queue `SYS.SCHEDULER$_EVENT_QUEUE`
ADD_WINDOW_GROUP_MEMBER Procedure	Adds a window to an existing window group
ALTER_CHAIN Procedure	Alters specified steps of a chain
ALTER_RUNNING_CHAIN Procedure	Alters specified steps of a running chain
CLOSE_WINDOW Procedure	Closes an open window prematurely
COPY_JOB Procedure	Copies an existing job
CREATE_CHAIN Procedure	Creates a chain, which is a named series of programs that are linked together for a combined objective
CREATE_EVENT_SCHEDULE Procedure	Creates an event schedule, which is a schedule that starts a job based on the detection of an event
CREATE_JOB Procedures	Creates a job
CREATE_JOB_CLASS Procedure	Creates a job class, which provides a way to group jobs for resource allocation and prioritization
CREATE_PROGRAM Procedure	Creates a program
CREATE_SCHEDULE Procedure	Creates a schedule
CREATE_WINDOW Procedures	Creates a window, which provides a way to automatically activate different resource plans at different times
CREATE_WINDOW_GROUP Procedure	Creates a window group
DEFINE_ANYDATA_ARGUMENT Procedure	Defines a program argument whose value is of a complex type and must be passed encapsulated in an `AnyData` object
DEFINE_CHAIN_EVENT_STEP Procedure	Adds or replaces a chain step and associates it with an event schedule or inline event. See also: `DEFINE_CHAIN_STEP`.
DEFINE_CHAIN_RULE Procedure	Adds a rule to an existing chain
DEFINE_CHAIN_STEP Procedure	Defines a chain step, which can be a program or another (nested) chain. See also: `DEFINE_CHAIN_EVENT_STEP`.
DEFINE_METADATA_ARGUMENT Procedure	Defines a special metadata argument for the program. You can retrieve specific metadata through this argument
DEFINE_PROGRAM_ARGUMENT Procedures	Defines a program argument whose value can be passed as a string literal to the program
DISABLE Procedure	Disables a program, job, chain, window, or window group
DROP_CHAIN Procedure	Drops an existing chain
DROP_CHAIN_RULE Procedure	Removes a rule from an existing chain
DROP_CHAIN_STEP Procedure	Drops a chain step
DROP_JOB Procedure	Drops a job or all jobs in a job class
DROP_JOB_CLASS Procedure	Drops a job class
DROP_PROGRAM Procedure	Drops a program
DROP_PROGRAM_ARGUMENT Procedures	Drops a program argument
DROP_SCHEDULE Procedure	Drops a schedule
DROP_WINDOW Procedure	Drops a window
DROP_WINDOW_GROUP Procedure	Drops a window group
ENABLE Procedure	Enables a program, job, chain, window, or window group
EVALUATE_CALENDAR_STRING Procedure	Evaluates the calendar string and tells you what the next execution date of a job or window will be
EVALUATE_RUNNING_CHAIN Procedure	Forces reevaluation of the rules of a running chain to trigger any rules for which the conditions have been satisfied
GENERATE_JOB_NAME Function	Generates a unique name for a job. This enables you to identify jobs by adding a prefix, so, for example, Sally's jobs would be named `sally1`, `sally2`, and so on
GET_ATTRIBUTE Procedure	Retrieves the value of an attribute of an object
GET_SCHEDULER_ATTRIBUTE Procedure	Retrieves the value of a Scheduler attribute
OPEN_WINDOW Procedure	Opens a window prematurely. The window is opened immediately for the duration
PURGE_LOG Procedure	Purges specific rows from the job and window logs
REMOVE_EVENT_QUEUE_SUBSCRIBER Procedure	Unsubscribes a user from the Scheduler event queue `SYS.SCHEDULER$_EVENT_QUEUE`
REMOVE_WINDOW_GROUP_MEMBER Procedure	Removes a window from an existing window group. This fails if the specified window is not a member of the given group
RESET_JOB_ARGUMENT_VALUE Procedures	Resets the current value assigned to an argument defined with the associated program
RUN_CHAIN Procedures	Immediately runs a chain by creating a run-once job
RUN_JOB Procedure	Runs a job immediately
SET_ATTRIBUTE Procedure	Changes an attribute of a job, schedule, or other Scheduler object
SET_ATTRIBUTE_NULL Procedure	Changes an attribute of an object to `NULL`
SET_JOB_ANYDATA_VALUE Procedures	Sets the value of a job argument encapsulated in an `AnyData` object
SET_JOB_ARGUMENT_VALUE Procedures	Sets the value of a job argument
SET_SCHEDULER_ATTRIBUTE Procedure	Sets the value of a Scheduler attribute
STOP_JOB Procedure	Stops a currently running job or all jobs in a job class

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

Table 93-3 ADD_EVENT_QUEUE_SUBSCRIBER Procedure Parameters

Parameter	Description
`subscriber_name`	Name of the Oracle Streams Advanced Queuing (AQ) agent to be used to subscribe to the Scheduler event queue. If `NULL`, an agent is created and assigned the user name of the calling user.

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

Table 93-4 ADD_WINDOW_GROUP_MEMBER Procedure Parameters

Parameter	Description
`group_name`	The name of the window group
`window_list`	The name of the window or windows

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

Table 93-5 ALTER_CHAIN Procedure Parameters

Parameter	Description
`chain_name`	The name of the chain to alter
`step_name`	The name of the step or a comma-separated list of steps to alter. This cannot be `NULL`.
`attribute`	The attribute of the steps to change. This must be one of: '`PAUSE`', '`SKIP`', or '`RESTART_ON_RECOVERY`' '`PAUSE`'—If the `PAUSE` attribute is set `TRUE` for a step, after the step has run, its state will be changed to `PAUSED` (and the `completed` attribute will remain `FALSE`). If `PAUSE` is reset to `FALSE` for a paused chain step (using `ALTER_RUNNING_CHAIN`), the state will be set to its completion state (`SUCCEEDED`, `FAILED`, or `STOPPED`) and the `completed` attribute will be set to `TRUE`. Setting `PAUSE` will have no effect on steps that have already run. This allows execution of a chain to be suspended after the execution of certain steps. '`SKIP`'—If the `SKIP` attribute is set `TRUE` for a step, when the step condition is met, instead of being run, the step is treated as if it has immediately succeeded. Setting `SKIP` to `TRUE` has no effect for a step that is running, scheduled to run after a delay, or has already run. If `SKIP` is set `TRUE` for a step that `PAUSE` is also set for, when the step condition is met, the step immediately changes to state `PAUSED`. '`RESTART_ON_RECOVERY`'—If the `RESTART_ON_RECOVERY` attribute is set to `TRUE` for a step, then if the step is stopped by a database shutdown, it is restarted when the database is recovered. If this attribute is set to `FALSE`, then if the step is stopped by a database shutdown, the step is marked as stopped when the database is recovered and the chain continues.
`value`	The value to set for the attribute. This must be one of: `TRUE`, `FALSE`.

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

Table 93-6 ALTER_RUNNING_CHAIN Procedure Parameters

Parameter	Description
`job_name`	The name of the job that is running the chain
`step_name`	The name of the step or a comma-separated list of steps to alter. If this is set to `NULL` and attribute is `PAUSE` or `SKIP`, then all steps of the running chain will be altered.
`attribute`	The attribute of the steps to change. This must be one of: '`PAUSE`', '`SKIP`', '`RESTART_ON_RECOVERY`', or '`STATE`'. '`PAUSE`'—If the `PAUSE` attribute is set `TRUE` for a step, after the step has run, its state will be changed to `PAUSED` (and the `completed` attribute will remain false). If `PAUSE` is reset to `FALSE` for a paused chain step (using `ALTER_RUNNING_CHAIN`), the state will be set to its completion state (`SUCCEEDED`, `FAILED`, or `STOPPED`) and the `completed` attribute will be set to `TRUE`. Setting `PAUSE` will have no effect on steps that have already run. This allows execution of a chain to be suspended after the execution of certain steps. If `step_name` is set to `NULL`, `PAUSE` will be set to `TRUE` for all steps of this running chain. '`SKIP`'—If the `SKIP` attribute is set to `TRUE` for a step, when the step condition is met, instead of being run, the step is treated as if it has immediately succeeded. Setting `SKIP` to `TRUE` has no effect for a step that is running, scheduled to run after a delay, or has already run. If `step_name` is set to `NULL`, `SKIP` will be set `TRUE` for all steps of this running chain. If `SKIP` is set `TRUE` for a step that `PAUSE` is also set for, when the step condition is met the step will immediately change to state `PAUSED`. '`RESTART_ON_RECOVERY`'—If the `RESTART_ON_RECOVERY` attribute is set to `TRUE` for a step, then if the step is stopped by a database shutdown, it is restarted when the database is recovered. If this attribute is set to `FALSE`, then if the step is stopped by a database shutdown, the step is marked as stopped when the database is recovered and the chain continues. '`STATE`'—This changes the state of the steps. The state can only be changed if the step is not running. The state can only be changed to one of the following: 'NOT_STARTED', 'SUCCEEDED', 'FAILED error_code' If the state is being changed to `FAILED`, an error code must be included (this must be a positive integer).
`value`	The value to set for the attribute. This must be one of: `TRUE`, `FALSE`, '`NOT_STARTED`', '`SUCCEEDED`', or '`FAILED` `error_code`'

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

Table 93-7 CLOSE_WINDOW Procedure Parameters

Parameter	Description
`window_name`	The name of the window

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

Table 93-8 COPY_JOB Procedure Parameters

Parameter	Description
`old_job`	The name of the existing job
`new_job`	The name of the new job

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

Table 93-9 CREATE_CHAIN Procedure Parameters

Parameter	Description
`chain_name`	The name of the new chain, which can optionally be qualified with a schema. This must be unique in the SQL namespace, so there must not already be a table or other object with this name and schema.
`rule_set_name`	In the normal case, no rule set should be passed in. The Scheduler will automatically create a rule set and associated empty evaluation context. You then use `DEFINE_CHAIN_RULE` to add rules and `DROP_CHAIN_RULE` to remove them. Advanced users can create a rule set that describes their chain dependencies and pass it in here. This allows greater flexibility in defining rules. For example, conditions can refer to external variables, and tables can be exposed through the evaluation context. If you pass in a rule set, you must ensure that it is in the format of a chain rule set. (For example, all steps must be listed as variables in the evaluation context). If no rule set is passed in, the rule set created will be of the form `SCHED_RULESET${N}` and the evaluation context created will be of the form `SCHED_EVCTX${N}` See Oracle Streams Concepts and Administration for information on rules and rule sets.
`evaluation_interval`	If this is `NULL`, reevaluation of the rules of a running chain are performed only when the job starts and when a step completes. A non-`NULL` value causes rule evaluations to also occur periodically at the specified interval. Because evaluation may be CPU-intensive, this should be conservatively set to the highest possible value or left at `NULL` if possible. `evaluation_interval` cannot be less than a minute or greater than a day.
`comments`	An optional comment describing the purpose of the chain

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_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

Table 93-10 CREATE_EVENT_SCHEDULE Parameters

Parameter	Description
`schedule_name`	This.attribute specifies a unique identifier for the schedule. The name has to be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs.
`start_date`	This attribute specifies the date on which this schedule becomes valid. Occurrences of the event before this date are ignored in the context of this schedule.
`event_condition`	This is a conditional expression based on the columns of the event source queue table. The expression must have the syntax of an Advanced Queuing rule. Accordingly, you can include user data properties in the expression provided that the message payload is an object type, and that you prefix object attributes in the expression with `tab.user_data`. For more information on rules, see the `DBMS_AQADM`.`ADD_SUBSCRIBER` procedure.
`queue_spec`	This argument specifies the queue into which events that start this particular job will be enqueued (the source queue). If the source queue is a secure queue, the `queue_spec` argument is a string containing a pair of values of the form queue_name, agent name. For non-secure queues, only the queue name need be provided. If a fully qualified queue name is not provided, the queue is assumed to be in the job owner's schema. In the case of secure queues, the agent name provided should belong to a valid agent that is currently subscribed to the queue.
`end_date`	The date after which jobs will not run and windows will not open. An event schedule that has no `end_date` is valid forever. `end_date` has to be after the `start_date`. If this is not the case, then an error will be generated when the schedule is created.
`comments`	This attribute specifies an optional comment about the schedule. By default, this attribute is `NULL`.

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 job.

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);

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);

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,
   event_queue             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);

Parameters

Table 93-11 CREATE_JOB Procedure Parameters

Parameter	Description
`job_name`	This attribute specifies the name of the job and uniquely identifies the job. The name has to be unique in the SQL namespace. For example, a job cannot have the same name as a table in a schema. If the job being created will reside in another schema, it must be qualified with the schema name. If `job_name` is not specified, an error is generated. If you want to have a name generated by the Scheduler, you can use the `GENERATE_JOB_NAME` procedure to generate a name and then use the output in the `CREATE_JOB` procedure. The `GENERATE_JOB_NAME` procedure call generates a number from a sequence, which is the job name. You can prefix the number with a string. The job name will then be the string with the number from the sequence appended to it. See "GENERATE_JOB_NAME Function" for more information.
`job_type`	This attribute specifies the type of job that you are creating. If it is not specified, an error is generated. The supported values are: `'PLSQL_BLOCK`' This specifies that the job is an anonymous PL/SQL block. Job or program arguments are not supported when the job or program type is `PLSQL_BLOCK`. In this case, the number of arguments must be 0. `'STORED_PROCEDURE'` This specifies that the job is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. `'EXECUTABLE'` This specifies that the job is a job external to the database. External jobs are anything that can be executed from the operating system's command line. `Anydata` arguments are not supported with a job or program type of `EXECUTABLE`. The job owner must have the `CREATE` `EXTERNAL` `JOB` system privilege before the job can be enabled or run. '`CHAIN`' This specifies that the job is a chain. Arguments are not supported for a chain, so `number_of_arguments` must be 0.
`job_action`	This attribute specifies the action of the job. The following actions are possible: For a PL/SQL block, the action is to execute PL/SQL code. These blocks must end with a semi-colon. For example, `my_proc();` or `BEGIN my_proc(); END;` or `DECLARE arg pls_integer := 10; BEGIN my_proc2(arg); END;`. Note that the Scheduler wraps `job_action` in its own block and passes the following to PL/SQL for execution: `DECLARE ... BEGIN job_action END;` This is done to declare some internal Scheduler variables. You can include any Scheduler metadata attribute except `event_message` in your PL/SQL code. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value. See Table 93-22 for details on available metadata attributes. For a stored procedure, the action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job. PL/SQL procedures with `INOUT` or `OUT` arguments are not supported as `job_action` when the job or program type is `STORED_PROCEDURE`. For an executable, the action is the name of the external executable, including the full path name and any command-line arguments. For a chain, the action is the name of a Scheduler chain object. You have to specify the schema of the chain if it resides in a different schema than the job. If `job_action` is not specified for an inline program, an error is generated when creating the job.
`number_of_arguments`	This attribute specifies the number of arguments that the job expects. The range is 0-255, with the default being 0.
`program_name`	The name of the program associated with this job. If the program is of type `EXECUTABLE`, the job owner must have the `CREATE` `EXTERNAL` `JOB` system privilege before the job can be enabled or run.
`start_date`	This attribute specifies the first date on which this job is scheduled to start. If `start_date` and `repeat_interval` are left null, then the job is scheduled to run as soon as the job is enabled. For repeating jobs that use a calendaring expression to specify the repeat interval, `start_date` is used as a reference date. The first time the job will be scheduled to run is the first match of the calendaring expression that is on or after the current date. The Scheduler cannot guarantee that a job will execute on an exact time because the system may be overloaded and thus resources unavailable.
`event_condition`	This is a conditional expression based on the columns of the event source queue table. The expression must have the syntax of an Advanced Queuing rule. Accordingly, you can include user data properties in the expression provided that the message payload is an object type, and that you prefix object attributes in the expression with `tab.user_data`. For more information on rules, see the `DBMS_AQADM`.`ADD_SUBSCRIBER` procedure.
`queue_spec`	This argument specifies the queue into which events that start this particular job will be enqueued (the source queue). If the source queue is a secure queue, the `queue_spec` argument is a string containing a pair of values of the form queue_name, agent name. For non-secure queues, only the queue name need be provided. If a fully qualified queue name is not provided, the queue is assumed to be in the job owner's schema. In the case of secure queues, the agent name provided should belong to a valid agent that is currently subscribed to the queue.
`repeat_interval`	This attribute specifies how often the job should repeat. You can specify the repeat interval by using calendaring or PL/SQL expressions. The expression specified is evaluated to determine the next time the job should run. If `repeat_interval` is not specified, the job will run only once at the specified start date. See "Calendaring Syntax" for further information.
`schedule_name`	The name of the schedule, window, or window group associated with this job.
`end_date`	This attribute specifies the date after which the job will expire and will no longer be executed. When `end_date` is reached, the job is disabled. The `STATE` of the job will be set to `COMPLETED`, and the `enabled` flag will be set to `FALSE`. If no value for `end_date` is specified, the job will repeat forever unless `max_runs` or `max_failures` is set, in which case the job stops when either value is reached. The value for `end_date` must be after the value for `start_date`. If it is not, an error is generated when the job is enabled.
job_priority	This attribute designates the priority of a job relative to other jobs in the same job class only. If two jobs in the same class are scheduled to start at the same time, the one with the higher priority takes precedence. Acceptable values are 1 through 5, where 1 is the highest priority. Default value is 3.
`comments`	This attribute specifies a comment about the job. By default, this attribute is `NULL`.
`enabled`	This attribute specifies whether the job is created enabled or not. The possible settings are `TRUE` or `FALSE`. By default, this attribute is set to `FALSE` and, therefore, the job is created as disabled. A disabled job means that the metadata about the job has been captured and the job exists as a database object but the Scheduler will ignore it and the job coordinator will not pick the job for processing. In order for the job coordinator to process the job, the job has to be enabled. You can enable a job by setting this argument to `TRUE` or by using the `ENABLE` procedure.
`auto_drop`	This flag, if `TRUE`, causes a job to be automatically dropped after it has completed or has been disabled. A job is considered completed if: Its end date (or its schedule's end date) has passed It has run `max_runs` number of times. `max_runs` must be set with `SET_ATTRIBUTE`. It is not a repeating job and has run once A job is disabled when it has failed `max_failures` times. `max_failures` is also set with `SET_ATTRIBUTE`. If this flag is set to `FALSE`, the jobs are not dropped and their metadata is kept until the job is explicitly dropped with the `DROP_JOB` procedure. By default, jobs are created with `auto_drop` set to `TRUE`.

Usage Notes

Jobs are created disabled by default. You must explicitly enable them so that they will become active and scheduled. Before enabling a job, ensure that all program arguments, if any, are defined, either by defining default values in the program object or by supplying values with the job.

To create a job in your own schema, you need to have the CREATE JOB privilege. A user with the CREATE ANY JOB privilege can create a job in any schema. If the job being created will reside in another schema, the job name must be qualified with the schema name. For a job of type EXECUTABLE (or for a job that points to a program of type EXECUTABLE), the job owner must have the CREATE EXTERNAL JOB system privilege before the job can be enabled or run.

Associating a job with a particular class or program requires EXECUTE privileges for that class or program.

Not all possible job attributes can be set with CREATE_JOB. Some must be set after the job is created. For example, job arguments must be set with the SET_JOB_ARGUMENT_VALUE Procedures or the SET_JOB_ANYDATA_VALUE Procedures. Other job attributes, such as job_priority and max_runs, are set with the SET_ATTRIBUTE Procedure.

Note:

The Scheduler runs event-based jobs for each occurrence of an event that matches the job's event condition. However, events that occur while the job is already running are ignored; the event gets consumed, but does not trigger another run of the job.

CREATE_JOB_CLASS Procedure

This procedure creates a job class. Job classes are created in the SYS schema.

Syntax

DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name            IN VARCHAR2,
   resource_consumer_group   IN VARCHAR2 DEFAULT NULL,
   service                   IN VARCHAR2 DEFAULT NULL,
   logging_level             IN PLS_INTEGER
                                DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
   log_history               IN PLS_INTEGER DEFAULT NULL,
   comments                  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 93-12 CREATE_JOB_CLASS Procedure Parameters

Parameter	Description
`job_class_name`	The name of the class being created. A schema other than `SYS` cannot be specified. This attribute specifies the name of the job class and uniquely identifies the job class. The name has to be unique in the SQL namespace. For example, a job class cannot have the same name as a table in a schema.
`resource_consumer_group`	This attribute specifies the resource consumer group this class is associated with. A resource consumer group is a set of synchronous or asynchronous sessions that are grouped together based on their processing needs. A job class has a many-to-one relationship with a resource consumer group. The resource consumer group that the job class associates with will determine the resources that will be allocated to the job class. If the resource consumer group that a job class is associated with is dropped, the job class will then be associated with the default resource consumer group. If no resource consumer group is specified, the job class is associated with the default resource consumer group. If the specified resource consumer group does not exist when creating the job class, an error occurs. If `resource_consumer_group` is specified, you cannot specify a service (it must be `NULL`). Also, if a service is specified, `resource_consumer_group` must be `NULL`.
`service`	This attribute specifies the database service that the jobs in this class will have affinity to. In a RAC environment, this means that the jobs in this class will only run on those database instances that are assigned to the specific service. If a service is specified, `resource_consumer_group` must be `NULL`. Note that a service can be mapped to a resource consumer group, so you can also control resources allocated to jobs by specifying a service. See `DBMS_RESOURCE_MANAGER`.`SET_CONSUMER_GROUP_MAPPING` for details. If no service is specified, the job class will belong to the default service, which means it will have no service affinity and any one of the database instances within the cluster might run the job. If the service that a job class belongs to is dropped, the job class will then belong to the default service. If the specified service does not exist when creating the job class, then an error occurs.
`logging_level`	This attribute specifies how much information is logged. The three possible options are: `DBMS_SCHEDULER.LOGGING_OFF` No logging will be performed for any jobs in this class. `DBMS_SCHEDULER.LOGGING_RUNS` The Scheduler will write detailed information to the job log for all runs of each job in this class. This is the default. `DBMS_SCHEDULER.LOGGING_FULL` In addition to recording every run of a job, the Scheduler will record all operations performed on all jobs in this class. In other words, every time a job is created, enabled, disabled, altered, and so on will be recorded in the log.
`log_history`	This enables you to control the amount of logging the Scheduler performs. To prevent the job log and the window log from growing indiscriminately, the Scheduler has an attribute that specifies how much history (in days) to keep. Once a day, the Scheduler will automatically purge all log entries from both the job log as well as the window log that are older than the specified history. The default is 30 days. You can change the default by using the `SET_SCHEDULER_ATTRIBUTE` procedure. For example, to change it to 90 days, issue the following statement: `DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE ('log_history','90');` The range of valid values is 1 through 999.
`comments`	This attribute is for an optional comment about the job class. By default, this attribute is `NULL`.

Usage Notes

For users to create jobs that belong to a job class, the job owner must have EXECUTE privileges on the job class. Therefore, after the job class has been created, EXECUTE privileges must be granted on the job class so that users create jobs belonging to that class. You can also grant the EXECUTE privilege to a role.

Creating a job class requires the MANAGE SCHEDULER system privilege.

CREATE_PROGRAM Procedure

This procedure creates a program.

Syntax

DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name             IN VARCHAR2,
   program_type             IN VARCHAR2,
   program_action           IN VARCHAR2,
   number_of_arguments      IN PLS_INTEGER DEFAULT 0,
   enabled                  IN BOOLEAN DEFAULT FALSE,
   comments                 IN VARCHAR2 DEFAULT NULL);

Parameters

Table 93-13 CREATE_PROGRAM Procedure Parameters

Parameter	Description
`program_name`	This attribute specifies a unique identifier for the program. The name has to be unique in the SQL namespace. For example, a program cannot have the same name as a table in a schema. If no name is specified, then an error occurs.
`program_type`	This attribute specifies the type of program you are creating. If it is not specified then you will get an error. There are three supported values for `program_type`: `'PLSQL_BLOCK'` This specifies that the program is a PL/SQL block. Job or program arguments are not supported when the job or program type is PLSQL_BLOCK. In this case, the number of arguments must be 0. `'STORED_PROCEDURE'` This specifies that the program is a PL/SQL or Java stored procedure, or an external C subprogram. Only procedures, not functions with return values, are supported. PL/SQL procedures with `INOUT` or `OUT` arguments are not supported. `'EXECUTABLE'` This specifies that the program is external to the database. External programs implies anything that can be executed from the operating system's command line. `AnyData` arguments are not supported with job or program type EXECUTABLE.
`program_action`	This attribute specifies the action of the program. The following actions are possible: For a PL/SQL block, the action is to execute PL/SQL code. These blocks must end with a semi-colon. For example, `my_proc();` or `BEGIN my_proc(); END;` or `DECLARE arg pls_integer := 10; BEGIN my_proc2(arg); END;`. Note that the Scheduler wraps `job_action` in its own block and passes the following to PL/SQL for execution: `DECLARE ... BEGIN job_action END;` This is done to declare some internal Scheduler variables. You can include any Scheduler metadata attribute except `event_message` in your PL/SQL code. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value. See Table 93-22 for details on available metadata attributes. For a stored procedure, the action is the name of the stored procedure. You have to specify the schema if the procedure resides in another schema than the job. For an executable, the action is the name of the external executable, including the full path name and any command-line arguments. If `program_action` is not specified, an error is generated If it is an anonymous block, special Scheduler metadata may be accessed using the following variable names: `job_name`, `job_owner`, `job_start`, `window_start`, `window_end`. For more information on these, see the information regarding `define_metadata_argument`.
`number_of_arguments`	This attribute specifies the number of arguments the program takes. If this parameter is not specified then the default will be 0. A program can have a maximum of 255 arguments. If the `program_type` is `PLSQL_BLOCK`, this field is ignored.
`enabled`	This flag specifies whether the program should be created enabled or not. If the flag is set to `TRUE`, then validity checks will be made and the program will be created `ENABLED` should all the checks be successful. By default, this flag is set to `FALSE`, which means that the program is not created enabled. You can also call the `ENABLE` procedure to enable the program before it can be used.
`comments`	A comment about the program. By default, this attribute is `NULL`.

Usage Notes

To create a program in his own schema, a user needs the CREATE JOB privilege. A user with the CREATE ANY JOB privilege can create a program in any schema. A program is created in a disabled state by default (unless the enabled field is set to TRUE). It cannot be executed by a job until it is enabled.

For other users to use your programs, they must have EXECUTE privileges, therefore once a program has been created, you have to grant EXECUTE privileges on it.

CREATE_SCHEDULE Procedure

This procedure creates a schedule.

Syntax

DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name          IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   repeat_interval        IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   comments               IN VARCHAR2 DEFAULT NULL);

Parameters

Table 93-14 CREATE_SCHEDULE Procedure Parameters

Parameter	Description
`schedule_name`	This attribute specifies a unique identifier for the schedule. The name has to be unique in the SQL namespace. For example, a schedule cannot have the same name as a table in a schema. If no name is specified, then an error occurs.
`start_date`	This attribute specifies the first date on which this schedule becomes valid. For a repeating schedule, the value for `start_date` is a reference date. In this case, the start of the schedule is not the `start_date`. It depends on the repeat interval specified. `start_date` is used to determine the first instance of the schedule. If `start_date` is specified in the past and no value for `repeat_interval` is specified, the schedule is invalid. For a repeating job or window, `start_date` can be derived from the `repeat_interval`, if it is not specified.
`repeat_interval`	This attribute specifies how often the schedule should repeat. It is expressed using calendaring syntax. See "Calendaring Syntax" for further information. PL/SQL expressions are not allowed as repeat intervals for named schedules.
`end_date`	The date after which jobs will not run and windows will not open. A non-repeating schedule that has no `end_date` will be valid forever. `end_date` has to be after the `start_date`. If this is not the case, then an error will be generated when the schedule is created.
`comments`	This attribute specifies an optional comment about the schedule. By default, this attribute is `NULL`.

Usage Notes

CREATE_WINDOW Procedures

This procedure creates a recurring time window and associates it with a resource plan. The window can then be used to schedule jobs, which run under the associated resource plan.

The procedure is overloaded.

Syntax

Creates a window using a named schedule object:

DBMS_SCHEDULER.CREATE_WINDOW (
   window_name             IN VARCHAR2,
   resource_plan           IN VARCHAR2,
   schedule_name           IN VARCHAR2,
   duration                IN INTERVAL DAY TO SECOND,
   window_priority         IN VARCHAR2                 DEFAULT 'LOW',
   comments                IN VARCHAR2                 DEFAULT NULL);

Creates a window using an inlined schedule:

DBMS_SCHEDULER.CREATE_WINDOW (
   window_name             IN VARCHAR2,
   resource_plan           IN VARCHAR2,
   start_date              IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval         IN VARCHAR2,
   end_date                IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   duration                IN INTERVAL DAY TO SECOND,
   window_priority         IN VARCHAR2                 DEFAULT 'LOW',
   comments                IN VARCHAR2                 DEFAULT NULL);

Parameters

Table 93-15 CREATE_WINDOW Procedure Parameters

Parameter	Description
`window_name`	This attribute uniquely identifies the window. The name has to be unique in the SQL namespace. All windows are in the `SYS` schema, so you can optionally preface the window name with '`SYS.`'
`resource_plan`	This attribute specifies the resource plan that is automatically activated when the window opens. When the window closes, the system switches to the appropriate resource plan, which in most cases is the resource plan that was in effect before the window opened, but can also be the resource plan of yet another window. Only one resource plan can be associated with a window. It may be `NULL` or the empty string (""). When it is `NULL`, the resource plan that is in effect when the window opens stays in effect for the duration of the window. When it is the empty string, the resource manager is disabled for the duration of the window. If the window is open and the resource plan is dropped, then the resource allocation for the duration of the window is not affected.
`start_date`	This attribute specifies the first date on which this window is scheduled to open. If the value for `start_date` specified is in the past or is not specified, the window opens as soon as it is created. For repeating windows that use a calendaring expression to specify the repeat interval, the value for `start_date` is a reference date. The first time the window opens depends on the repeat interval specified and the value for `start_date`.
`duration`	This attribute specifies how long the window will be open for. For example, `'interval '5' hour'` for five hours. There is no default value for this attribute. Therefore, if none is specified when creating the window, an error occurs. The duration is of type interval day to seconds and ranges from one minute to 99 days.
`schedule_name`	The name of the schedule associated with the window.
`repeat_interval`	This attribute specifies how often the window should repeat. It is expressed using the Scheduler's calendaring syntax. See "Calendaring Syntax" for more information. A PL/SQL expression cannot be used to specify the repeat interval for a window. The expression specified is evaluated to determine the next time the window should open. If no repeat_interval is specified, the window will open only once at the specified start date.
`end_date`	This attribute specifies the date after which the window will no longer open. When the value for `end_date` is reached, the window is disabled. In the `*_SCHEDULER_WINDOWS` views, the enabled flag of the window will be set to `FALSE`. A non-repeating window that has no value for end_date opens only once for the duration of the window. For a repeating window, if no `end_date` is specified then the window will keep repeating forever. The `end_date` has to be after the start_date. If this is not the case, then an error is generated when the window is created.
`window_priority`	This attribute is only relevant when two windows overlap. Because only one window can be in effect at one time, the window priority will be used to determine which window will be opened. The two possible values for this attribute are '`HIGH`' and '`LOW`'. A high priority window has precedence over a low priority window, which implies that the low priority window does not open if it overlaps with a high priority window. By default, a window is created wit