Skip Headers
Oracle® Database PL/SQL Packages and Types Reference
11g Release 1 (11.1)

B28419-03
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

114 DBMS_SCHEDULER

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

See Also:

Oracle Database Administrator's Guide for more information regarding how to use DBMS_SCHEDULER

This chapter contains the following topics:


Data Structures

The DBMS_SCHEDULER package defines OBJECT types and TABLE types.

OBJECT Types

TABLE Types


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

Table 114-1 JOBARG Object Type Attributes

Attribute Description

arg_position

Position of the argument

arg_text_value

Value of the argument if the type is VARCHAR2

arg_anydata_value

Value of the argument if the type is AnyData

arg_operation

Type of the operation:

  • SET

  • RESET


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

Table 114-2 JOBARG Constructor Function Parameters

Parameter Description

arg_position

Position of the argument

arg_value

Value of the argument

arg_reset

If arg_reset is TRUE, then the argument at that position is reset.

Setting arg_reset to FALSE (which is the default) will create an argument with a NULL value.



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

Table 114-3 JOB Object Type Attributes

Attribute Description

job_name

Name of the job

job_class

Name of the job class

job_style

Style of the job:

  • REGULAR

  • LIGHTWEIGHT

job_template

Name of the program. Equivalent to the program_name argument of the CREATE_JOB Procedure.

program_action

Inline action of the job. Equivalent to the job_action argument of the CREATE_JOB Procedure.

action_type

Job action type. Equivalent to the job_type argument of the CREATE_JOB Procedure. Either job_template must be set and program_action and action_type left null, or program_action and action_type must be set and job_template left null.

schedule_name

Name of the schedule that specifies when the job has to execute

repeat_interval

Inline time-based schedule

schedule_limit

Time from the scheduled execution time that the job should be run

start_date

Start date and time of the job

end_date

End date and time of the job

event_condition

Event condition for event-based jobs

queue_spec

Queue specification for event-based jobs

number_of_args

Number of job arguments

arguments

Array of job arguments

priority

Job priority

job_weight

Weight of the job

max_run_duration

Maximum run duration of the job

max_runs

Maximum number of runs before the job is marked as completed

max_failures

Maximum number of failures to tolerate before the job is marked as broken

logging_level

Job logging level

restartable

Indicates whether the job is restartable (TRUE) or not (FALSE)

stop_on_window_exit

Indicates whether the job should be stopped when the window it is running in ends (TRUE) or not (FALSE). Equivalent to the stop_on_window_close job attribute described in the SET_ATTRIBUTE Procedure.

raise_events

State changes on which events should be raised

comments

Comments on the job

auto_drop

If TRUE (the default), indicates that the job should be dropped once completed

enabled

Indicates whether the job should be enabled immediately after creating it (TRUE) or not (FALSE)

follow_default_tz

If TRUE and if the job start_date is null, then when the default_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone. For example, if the job was set to run at 02:00 in the previous time zone, it will run at 02:00 in the new time zone. (If the job start_date is not null, then the time zone for the run date and time for the job is always specified by the time zone of the start_date.) If FALSE, the next start date and time for the job is not recomputed when the default_timezone scheduler attribute is changed. In this case, if the old time zone is three hours earlier than the new time zone, then a job scheduled to run at 02:00 in the old time zone runs at 05:00 in the new time zone.

parallel_instances

For event-based jobs only. If TRUE, on the arrival of the specified event, the Scheduler creates a new lightweight job to handle that event, so multiple instances of the same event-based job can run in parallel.

aq_job

For internal use only.

instance_id

The instance ID of the instance that the job must run on.


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

Table 114-4 JOBATTR Object Type Attributes

Attribute Description

job_name

Name of the job

attr_name

Name of the attribute

char_value

Value of the argument if the type is VARCHAR2

char_value2

Second VARCHAR2 attribute value

args_value

Value of the argument if the type is a JOBARG array

num_value

Value of the argument if the type is NUMBER

timestamp_value

Value of the argument if the type is TIMESTAMP WITH TIME ZONE

interval_value

Value of the argument if the type is INTERVAL DAY TO SECOND


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

Table 114-5 JOBATTR Constructor Function Parameters

Parameter Description

job_name

Name of the job

attr_name

Name of the argument

attr_value

Value of the argument

attr_value2

Most attributes have only one value associated with them, but some can have two. The attr_value2 argument is for this optional second value.



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

Table 114-6 SCHEDULER$_STEP_TYPE Object Type

Attribute Description

step_name

Name of the step

step_type

State of the step



SCHEDULER$_STEP_TYPE_LIST Table Type

Syntax

TYPE scheduler$_step_type_list IS TABLE OF scheduler$_step_type;

Using DBMS_SCHEDULER

This section contains:


Rules and Limits

The following rules apply when using the DBMS_SCHEDULER package:


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

In the following calendaring syntax, * means 0 or more.

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 = "-"

Table 114-7 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 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:

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:

BYSETPOS Clause Rules The following are rules for the BYSETPOS clause.

BYDATE Clause Rules The following are rules for the BYDATE clause.

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 114-1 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 114-8 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_CREDENTIAL Procedure

Creates a credential

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 Procedure

Creates a single job

CREATE_JOB_CLASS Procedure

Creates a job class, which provides a way to group jobs for resource allocation and prioritization

CREATE_JOBS Procedure

Creates multiple jobs

CREATE_PROGRAM Procedure

Creates a program

CREATE_SCHEDULE Procedure

Creates a schedule

CREATE_WINDOW Procedure

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 Procedure

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_CREDENTIAL Procedure

Drops a credential

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 Procedure

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

END_DETACHED_JOB_RUN Procedure

Ends a running detached job

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_FILE Procedure

Retrieves a file from a host

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

PUT_FILE Procedure

Saves a file to one or more hosts

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 Procedure

Resets the current value assigned to an argument defined with the associated program

RUN_CHAIN Procedure

Immediately runs a chain by creating a run-once job

RUN_JOB Procedure

Runs a job immediately

SET_AGENT_REGISTRATION_PASS Procedure

Sets the agent registration password for a database

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 Procedure

Sets the value of a job argument encapsulated in an AnyData object

SET_JOB_ARGUMENT_VALUE Procedure

Sets the value of a job argument

SET_JOB_ATTRIBUTES Procedure

Sets the value of a job attribute

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 114-9 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 114-10 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 an attribute of the specified steps of a chain. This affects all future runs of the specified steps, both in the currently running chain job and in future runs of the same chain job or other chain jobs that point to the chain.

Syntax

Alters the value of a boolean attribute of one or more steps:

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

Alters the value of a character attribute of one or more steps:

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

Parameters

Table 114-11 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. Must be one of the following:

  • '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.

  • 'DESTINATION'—Valid only for steps that run remote external jobs. Specifies the destination where the step is to run. Must be of the form host:port, where host is the host name or IP address of the destination host, and port is the port number on which the Scheduler agent on that host listens. NULL by default.

  • 'CREDENTIAL_NAME'—Valid only for steps that run external jobs (local or remote). Specifies the credential to use when running this step. NULL by default.

value

The value to set for the attribute (for a boolean attribute).

char_value

The value to set for the attribute (for a character attribute).


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 an attribute of the specified steps of a chain. This affects only steps of the instance of the chain for the specified running chain job.

Syntax

DBMS_SCHEDULER.ALTER_RUNNING_CHAIN (
   job_name                IN VARCHAR2,
   step_name               IN VARCHAR2,
   attribute               IN VARCHAR2,
   value                   IN {BOOLEAN|VARCHAR2});

Parameters

Table 114-12 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 114-13 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 114-14 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 114-15 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_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

Table 114-16 CREATE_CREDENTIAL Procedure Parameters

Parameter Description

credential_name

This is the name that will be used to refer to the credential. It can optionally be prefixed with a schema. This cannot be set to NULL. It is converted to upper case unless enclosed in double-quotes.

username

This is the user name that will be used to login to the operating system to run a job if this credential is chosen. This cannot be set to NULL.

password

This is the password that will be used to login to the remote operating system to run a job if this credential is chosen. This cannot be set to NULL and is case sensitive. The password is stored obfuscated and is not displayed in the Scheduler dictionary views.

database_role

Reserved for future use.

windows_domain

For a Windows remote executable target, this is the domain that the specified user belongs to. The domain will be converted to uppercase automatically.

comments

This is a text string that can be used to describe the credential. This field is not used by the Scheduler.


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

Table 114-17 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 and time on which this schedule becomes valid. Occurrences of the event before this date and time 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 and time 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 Procedure

This procedure creates a single job (regular or lightweight). If you create the job enabled by setting the enabled attribute to TRUE, the Scheduler automatically runs the job according to its schedule. If you create the job disabled, the job does not run until you enable it with the SET_ATTRIBUTE Procedure.

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

Table 114-18 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 114-30 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, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at-sign ('@') and the job is local, the at-sign is replaced with the SID of the current Oracle instance.

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 and time 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 and time.

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.

job_class

The class this job is associated with.

end_date

This attribute specifies the date and time after which the job expires and is no longer run. After the end_date, if auto_drop is TRUE, the job is dropped. If auto_drop is FALSE, the job is disabled and the STATE of the job is set to COMPLETED.

If no value for end_date is specified, the job repeats 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.

comments

This attribute specifies a comment about the job. By default, this attribute is NULL.

job_style

Style of the job to create. This argument can have one of the following values:

  • 'REGULAR' - a regular job will be created. This is the default.

  • 'LIGHTWEIGHT' - a lightweight job is created. This value is permitted only when the job references a program object. Use lightweight jobs when you have many short-duration jobs that run frequently. Under certain circumstances, using lightweight jobs can deliver a small performance gain.

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 automatically 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 Procedure or the SET_JOB_ANYDATA_VALUE Procedure. Other job attributes, such as job_priority and max_runs, are set with the SET_ATTRIBUTE Procedure.

To create multiple jobs efficiently, use the CREATE_JOBS 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 114-19 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.

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.

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 both the resource_consumer_group and service attributes are specified, and if the service is mapped to a resource consumer group, the resource_consumer_group attribute takes precedence.

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 possible options are:

  • DBMS_SCHEDULER.LOGGING_OFF

    No logging is performed for any jobs in this class.

  • DBMS_SCHEDULER.LOGGING_RUNS

    The Scheduler writes detailed information to the job log for all runs of each job in this class. This is the default.

  • DBMS_SCHEDULER.LOGGING_FAILED_RUNS

    The Scheduler logs only jobs that failed in this class.

  • DBMS_SCHEDULER.LOGGING_FULL

    In addition to recording every run of a job, the Scheduler records all operations performed on all jobs in this class. Every time a job is created, enabled, disabled, altered (with SET_ATTRIBUTE), stopped, and so, an entry is recorded in the log.

log_history

This attribute controls the number of days that job log entries for jobs in this class are retained. It helps prevent the job log from growing indiscriminately.

The range of valid values is 0 through 999. If set to 0, no history is kept. If NULL (the default), retention days are set by the log_history Scheduler attribute (set with SET_SCHEDULER_ATTRIBUTE).

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_JOBS Procedure

This procedure creates multiple jobs (regular or lightweight) and sets the values of their arguments in a single call.

Syntax

DBMS_SCHEDULER.CREATE_JOBS (
   job_array         IN JOB_ARRAY,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-20 CREATE_JOBS Procedure Parameters

Parameter Description

job_array

The array of job definitions. See "Data Structures" for a description of the JOB_ARRAY and JOB data types.

commit_semantics

The commit semantics. The following types are supported:

  • STOP_ON_FIRST_ERROR - this procedure returns on the first error and the previous creates that were successful are committed to disk. This is the default.

  • TRANSACTIONAL - this procedure returns on the first error and everything that happened before that error is rolled back.

  • ABSORB_ERRORS - this procedure tries to absorb any errors and attempts to create the rest of the jobs on the list and commits all the creates that were successful.


Usage Notes

This procedure allows for the creation of a large number of jobs in the context of a single transaction. To realize the desired performance gains, the jobs to create must be grouped into batches of sufficient size. Calling CREATE_JOBS with a small array size may not be much faster than calling CREATE_JOB once for each job.


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 114-21 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 114-30 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, but excluding any command-line arguments. If the action starts with a single question mark ('?'), the question mark is replaced by the path to the Oracle home directory for a local job or to the Scheduler agent home for a remote job. If the action contains an at-sign ('@') and the job is local, the at-sign is replaced with the SID of the current Oracle instance.

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 114-22 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 and time 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.

If start_date is null, then the date that the job or window is enabled is used. start_date and repeat_interval cannot both be null.

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 and time 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

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_WINDOW Procedure

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. Windows are created in the SYS schema.

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 114-23 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 and time 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 and time 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 with a priority of 'LOW'.

comments

This attribute specifies an optional comment about the window. By default, this attribute is NULL.


Usage Notes

Creating a window requires the MANAGE SCHEDULER privilege.

Scheduler windows are the principal mechanism used to automatically switch resource plans according to a schedule. You can also manually activate a resource plan by using the ALTER SYSTEM SET RESOURCE_MANAGER_PLAN statement or the DBMS_RESOURCE_MANAGER.SWITCH_PLAN package procedure. Note that either of these manual methods can also disable resource plan switching by Scheduler windows. For more information, see Oracle Database Administrator's Guide and "SWITCH_PLAN Procedure".


CREATE_WINDOW_GROUP Procedure

This procedure creates a new window group. A window group is defined by a list of Scheduler windows. You can assign a window group as a job schedule. The job then runs when any of the windows in the group become active.

Window groups are created in the SYS schema.

Syntax

DBMS_SCHEDULER.CREATE_WINDOW_GROUP (
   group_name            IN VARCHAR2,
   window_list           IN VARCHAR2 DEFAULT NULL,
   comments              IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-24 CREATE_WINDOW_GROUP Procedure Parameters

Parameter Description

group_name

The name of the window group

window_list

A list of the windows assigned to the window group. If a window that does not exist is specified, an error is generated and the window group is not created.

Windows can also be added using the ADD_WINDOW_GROUP_MEMBER procedure. A window group cannot be a member of another window group.

Can be NULL.

comments

A comment about the window group


Usage Notes

Creating a window group requires the MANAGE SCHEDULER privilege. Window groups, like windows, are created with access to PUBLIC, therefore, no privileges are required to access window groups.

A window group cannot contain another window group.


DEFINE_ANYDATA_ARGUMENT Procedure

This procedure defines a name or default value for a program argument that is of a complex type and must be encapsulated within an ANYDATA object. A job that references the program can override the default value.

Syntax

DBMS_SCHEDULER.DEFINE_ANYDATA_ARGUMENT (
   program_name            IN VARCHAR2,
   argument_position       IN PLS_INTEGER,
   argument_name           IN VARCHAR2 DEFAULT NULL,
   argument_type           IN VARCHAR2,
   default_value           IN SYS.ANYDATA,
   out_argument            IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-25 DEFINE_ANYDATA_ARGUMENT Procedure Parameters

Parameter Description

program_name

The name of the program to be altered. A program with this name must exist.

argument_position

The position of the argument as it is passed to the executable. Argument numbers go from one to the number_of_arguments specified for the program. This must be unique, so it will replace any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ANYDATA_VALUE Procedure.

argument_type

The data type of the argument being defined. This is not verified or used by the Scheduler. It is only used by the user of the program when deciding what value to assign to the argument.

default_value

The default value to be assigned to the argument encapsulated within an AnyData object. This is optional.

out_argument

This parameter is reserved for future use. It must be set to FALSE.


Usage Notes

All program arguments from 1 to the number_of_arguments value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.

Defining a program argument requires that you be the owner of the program or have ALTER privileges on that program. You can also define a program argument if you have the CREATE ANY JOB privilege.


DEFINE_CHAIN_EVENT_STEP Procedure

This procedure adds or replaces a chain step and associates it with an event schedule or an inline event. Once started in a running chain, this step will not complete until the specified event has occurred. Every step in a chain must be defined before the chain can be enabled and used. Defining a step gives it a name and specifies what happens during the step. If a step already exists with this name, the new step will replace the old one.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   event_schedule_name     IN VARCHAR2,
   timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);

DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   event_condition         IN VARCHAR2,
   queue_spec              IN VARCHAR2,
   timeout                 IN INTERVAL DAY TO SECOND DEFAULT NULL);

Parameters

Table 114-26 DEFINE_CHAIN_EVENT_STEP Procedure Parameters

Parameter Description

chain_name

The name of the chain that the step is in

step_name

The name of the step

event_schedule_name

The name of the event schedule that the step waits for

timeout

This parameter is reserved for future use

event_condition

See the CREATE_EVENT_SCHEDULE Procedure

queue_spec

See the CREATE_EVENT_SCHEDULE Procedure


Usage Notes

Defining a chain step 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.


DEFINE_CHAIN_RULE Procedure

This procedure adds a new rule to an existing chain, specified as a condition-action pair. The condition is expressed using either SQL or the Scheduler chain condition syntax, and indicates the prerequisites for the action to occur. The action specifies what is to be done as a result of the condition being met.

An actual rule object is created to store the rule in the schema in which the chain resides. If a rule name is given, this name will be used for the rule object. If a rule name is given and a rule already exists with this name in the chain's schema, the existing rule will be altered. (A schema different than the chain's schema cannot be specified). If no rule name is given, one will be generated of the form SCHED_RULE${N}.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   chain_name              IN VARCHAR2,
   condition               IN VARCHAR2,
   action                  IN VARCHAR2,
   rule_name               IN VARCHAR2 DEFAULT NULL,
   comments                IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-27 DEFINE_CHAIN_RULE Procedure Parameters

Parameter Description

chain_name

The name of the chain to alter

condition

A boolean expression expressed using either SQL or the Scheduler chain condition syntax. (Scheduler chain condition syntax is described below.) The expression must evaluate to TRUE for the action to be performed.

If the condition is expressed with SQL, it must use the syntax of a SELECT statement WHERE clause. You can refer to chain step attributes by using the chain step name as a bind variable. The bind variable syntax is :step_name.attribute. (step_name refers to a typed object.) Possible attributes are: completed, state, start_date, end_date, error_code, and duration. Possible values for the state attribute include: 'NOT_STARTED', 'SCHEDULED', 'RUNNING', 'PAUSED', 'STALLED', 'SUCCEEDED', 'FAILED', and 'STOPPED'. If a step is in the state 'SUCCEEDED', 'FAILED', or 'STOPPED', its completed attribute is set to 'TRUE', otherwise completed is 'FALSE'.

Every chain must have a rule that evaluates to TRUE to start the chain. For this purpose, you can use a rule that has 'TRUE' as its condition if you are using Schedule chain condition syntax, or '1=1' as its condition if you are using SQL syntax.

action

The action to be performed when the rule evaluates to TRUE. The action must consist of at least a keyword with an optional value and an optional delay clause.

Possible actions include:

  • [AFTER delay_interval] START step_1[,step_2 ...]

  • STOP step_1[,step_2 ...]

  • END [{end_value|step_name.error_code}]

At the beginning of the START action, a delay clause can be given which specifies a delay interval to wait before performing the action. delay_interval is a formatted datetime interval of the form HH:MM:SS.

The END action ends the chain with an error code equal to either the supplied end_value or the error code that step_name completed with. The default error code is 0, indicating a successful chain run.

rule_name

The name of the rule that will be created. If no rule_name is given, one will be generated of the form SCHED_RULE$_{N}.

comments

An optional comment describing the rule. This will be stored in the rule object created.


Chain Condition Syntax

The Scheduler chain condition syntax provides an easy way to construct a condition using the states and error codes of steps in the current chain. The following are the available constructs, all of which are boolean expressions:

TRUE
FALSE
stepname [NOT] SUCCEEDED 
stepname [NOT] FAILED 
stepname [NOT] STOPPED 
stepname [NOT] COMPLETED 
stepname ERROR_CODE IN (integer, integer, integer ...)
stepname ERROR_CODE NOT IN (integer, integer, integer ...)
stepname ERROR_CODE = integer
stepname ERROR_CODE != integer
stepname ERROR_CODE <> integer
stepname ERROR_CODE > integer
stepname ERROR_CODE >= integer
stepname ERROR_CODE < integer
stepname ERROR_CODE <= integer

The following boolean operators are available to create more complex conditions:

expression AND expression
expression OR expression
NOT (expression)

integer can be positive or negative. Parentheses may be used for clarity or to enforce ordering. You must use parentheses with the NOT operator.

PL/SQL code that runs as part of a step can set the value of ERROR_CODE for that step with the RAISE_APPLICATION_ERROR statement.

Usage Notes

Defining a chain rule requires ALTER privileges on the chain (either by being the owner, or by having ALTER privileges on the chain or by having the CREATE ANY JOB system privilege).

You must define at least one rule that starts the chain and at least one that ends it. See the section "Adding Rules to a Chain" in Oracle Database Administrator's Guide for more information.

Examples

The following are examples of using rule conditions and rule actions.

Rule Conditions Using Scheduler Chain Condition Syntax

'step1 completed'
-- satisfied when step step1 has completed. (step1 completed is also TRUE when any 
-- of the following are TRUE: step1 succeeded, step1 failed, step1 stopped.)

'step1 succeeded and step2 succeeded'
-- satisfied when steps step1 and step2 have both succeeded

'step1 error_code > 100'
-- satisfied when step step1 has failed with an error_code greater than 100

'step1 error_code IN (1, 3, 5, 7)'
-- satisfied when step step1 has failed with an error_code of 1, 3, 5, or 7

Rule Conditions Using SQL Syntax

':step1.completed = ''TRUE'' AND :step1.end_date >SYSDATE-1/24'
--satisfied when step step1 completed less than an hour ago

':step1.duration > interval ''5'' minute'
-- satisfied when step step1 has completed and took longer than 5 minutes to complete

Rule Actions

'AFTER 01:00:00 START step1, step2'
--After an hour start steps step1 and step2

'STOP step1'
--Stop step step1

END step4.error_code'
--End the chain with the error code that step step4 finished with. If step4 has not completed, the chain will be ended unsuccessfully with error code 27435.

'END' or 'END 0'
--End the chain successfully (with error_code 0)

'END 100'
--End the chain unsuccessfully with error code 100.

DEFINE_CHAIN_STEP Procedure

This procedure adds or replaces a chain step and associates it with a program or a nested chain. When the chain step is started, the specified program or chain is run. If a step already exists with the name supplied in the chain_name argument, the new step replaces the old one.

The chain owner must have EXECUTE privileges on the program or chain associated with the step. Only one program or chain can run during a step.

Syntax

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   program_name            IN VARCHAR2);

Parameters

Table 114-28 DEFINE_CHAIN_STEP Procedure Parameters

Parameter Description

chain_name

The name of the chain to alter.

step_name

The name of the step being defined. If a step already exists with this name, the new step will replace the old one.

program_name

The name of a program or chain to run during this step. The chain owner must have EXECUTE privileges on this program or chain.


Usage Notes

Defining a chain step requires ALTER privileges on the chain (either by being the owner, or by having ALTER privileges on the chain or by having the CREATE ANY JOB system privilege).


DEFINE_METADATA_ARGUMENT Procedure

This procedure defines a special metadata argument for the program. The Scheduler can pass Scheduler metadata through this argument to your stored procedure or other executable. You cannot set values for jobs using this argument.

Syntax

DBMS_SCHEDULER.DEFINE_METADATA_ARGUMENT (
  program_name            IN VARCHAR2,
  metadata_attribute      IN VARCHAR2,
  argument_position       IN PLS_INTEGER,
  argument_name           IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-29 DEFINE_METADATA_ARGUMENT Procedure Parameters

Parameter Description

program_name

The name of the program to be altered

metadata_attribute

The metadata to be passed. Valid metadata attributes are: 'job_name', 'job_subname', 'job_owner', 'job_start', 'window_start', 'window_end', and 'event_message'.

Table Table 114-30 describes these attributes in detail.

argument_position

The position of the argument as it is passed to the executable. This cannot be greater than the number_of_arguments specified for the program. This must be unique, so it will replace any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures.


Table 114-30 Metadata Attributes

Metadata Attribute Data Type Description

job_name

VARCHAR2

Name of the currently running job

job_subname

VARCHAR2

Subname of the currently running job. The name + subname form a unique identifier for a job that is running a chain step. NULL if the job is not part of a chain.

job_owner

VARCHAR2

Owner of the currently running job

job_scheduled_start

TIMESTAMP WITH TIME ZONE

When the currently running job was scheduled to start

job_start

TIMESTAMP WITH TIME ZONE

When the currently running job started

window_start

TIMESTAMP WITH TIME ZONE

If the job was started by a window, the time that the window opened

window_end

TIMESTAMP WITH TIME ZONE

If the job was started by a window, the time that the window is scheduled to close

event_message

(See Description)

For an event-based job, the message content of the event that started the job. The data type of this attribute depends on the queue used for the event. It has the same type as the USER_DATA column of the queue table.


Usage Notes

Defining a program argument requires that you be the owner of the program or have ALTER privileges on that program. You can also define a program argument if you have the CREATE ANY JOB privilege.

All metadata attributes except event_message can also be used in PL/SQL blocks that you enter into the job_action or program_action attributes of jobs or programs, respectively. You use the attribute name as you use any other PL/SQL identifier, and the Scheduler assigns it a value.


DEFINE_PROGRAM_ARGUMENT Procedure

This procedure defines a name or default value for a program argument. If no default value is defined for a program argument, the job that references the program must supply an argument value. (The job can also override a default value.)

This procedure is overloaded.

Syntax

Defines a program argument without a default value:

PROCEDURE define_program_argument(
   program_name            IN VARCHAR2,
   argument_position       IN PLS_INTEGER,
   argument_name           IN VARCHAR2 DEFAULT NULL,
   argument_type           IN VARCHAR2,
   out_argument            IN BOOLEAN DEFAULT FALSE);

Defines a program argument with a default value:

PROCEDURE define_program_argument(
   program_name            IN VARCHAR2,
   argument_position       IN PLS_INTEGER,
   argument_name           IN VARCHAR2 DEFAULT NULL,
   argument_type           IN VARCHAR2,
   default_value           IN VARCHAR2,
   out_argument            IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-31 DEFINE_PROGRAM_ARGUMENT Procedure Parameters

Parameter Description

program_name

The name of the program to be altered. A program with this name must exist.

argument_position

The position of the argument as it is passed to the executable. Argument numbers go from one to the number_of_arguments specified for the program. This must be unique so it will replace any argument already defined at this position.

argument_name

The name to assign to the argument. It is optional, but must be unique for the program if it is specified. If you assign a name, the name can then be used by other package procedures, including the SET_JOB_ARGUMENT_VALUE Procedure.

argument_type

The data type of the argument being defined. This is not verified or used by the Scheduler. It is only used by the user of the program when deciding what value to assign to the argument.

default_value

The default value to be assigned to the argument if none is specified by the job.

out_argument

This parameter is reserved for future use. It must be set to FALSE.


Usage Notes

All program arguments from 1 to the number_of_arguments value must be defined before a program can be enabled. If a default value for an argument is not defined with this procedure, a value must be defined in the job.

Defining a program argument requires that you be the owner of the program or have ALTER privileges on that program. You can also define a program argument if you have the CREATE ANY JOB privilege.


DISABLE Procedure

This procedure disables a program, job, chain, window, or window group.

Syntax

DBMS_SCHEDULER.DISABLE (
   name              IN VARCHAR2,
   force             IN BOOLEAN DEFAULT FALSE,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-32 DISABLE Procedure Parameters

Parameter Description

name

The name of the object being disabled. Can be a comma-delimited list.

If a job class name is specified, then all the jobs in the job class are disabled. The job class is not disabled.

If a window group name is specified, then the window group will be disabled, but the windows that are members of the window group will not be disabled.

force

Whether to ignore dependencies. See the usage notes for more information.

commit_semantics

The commit semantics. The following types are supported:

  • STOP_ON_FIRST_ERROR - The procedure returns on the first error and the previous disable operations that were successful are committed to disk. This is the default.

  • TRANSACTIONAL - This value permitted only when disabling jobs. The procedure returns on the first error and everything that happened before that error is rolled back. This type is only supported when disabling a job or a list of jobs. In addition, this type is not supported when force is set to TRUE.

  • ABSORB_ERRORS - This value permitted only when disabling jobs. The procedure tries to absorb any errors and attempts disabling the rest of the jobs and commits all the disable operations that were successful. This type is only supported when disabling a job or a list of jobs.


Usage Notes

Disabling an object that is already disabled does not generate an error. Because the DISABLE procedure is used for several Scheduler objects, when disabling windows and window groups, they must be preceded by SYS.

The purpose of the force option is to point out dependencies. No dependent objects are altered.

To run DISABLE for a window or window group, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being disabled or have ALTER privileges on that object or have the CREATE ANY JOB privilege.

Jobs

Disabling a job means that, although the metadata of the job is there, it should not run and the job coordinator will not pick up these jobs for processing. When a job is disabled, its state in the job queue is changed to disabled.

If force is set to FALSE and the job is currently running, an error is returned.

If force is set to TRUE, the job is disabled, but the currently running instance is allowed to finish.

Programs

When a program is disabled, the status is changed to disabled. A disabled program implies that, although the metadata is still there, jobs that point to this program cannot run.

If force is set to FALSE, the program must not be referenced by any job, otherwise an error will occur.

If force is set to TRUE, those jobs that point to the program will not be disabled, however, they will fail at runtime because their program will not be valid.

Running jobs that point to the program are not affected by the DISABLE call, and are allowed to continue

Any argument that pertains to the program will not be affected when the program is disabled.

Windows

This means that the window will not open, however, the metadata of the window is still there, so it can be reenabled.

If force is set to FALSE, the window must not be open or referenced by any job otherwise an error will occur.

If force is set to TRUE, disabling a window that is open will succeed but the window will not be closed. It will prevent the window from opening in the future until it is re-enabled.

When the window is disabled, those jobs that have the window as their schedule will not be disabled.

Window Groups

When a window group is disabled, jobs, other than a running job, that has the window group as its schedule will not run even if the member windows open. However, if the job had one of the window group members as its schedule, it would still run.

The metadata of the window group is still there, so it can be reenabled. Note that the members of the window group will still open.

If force is set to FALSE, the window group must not have any members that are open or referenced by any job otherwise an error will occur.

If force is set to TRUE:

Job Chains

When a chain is disabled, the metadata for the chain is still there, but jobs that point to it will not be able to be run. This allows changes to the chain to be made safely without the risk of having an incompletely specified chain run.If force is set to FALSE, the chain must not be referenced by any job, otherwise an error will occur.If force is set to TRUE, those jobs that point to the chain will not be disabled, however, they will fail at runtime.Running jobs that point to this chain are not affected by the DISABLE call and are allowed to complete.


DROP_CHAIN Procedure

This procedure drops an existing chain.

Syntax

DBMS_SCHEDULER.DROP_CHAIN (
   chain_name              IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-33 DROP_CHAIN Procedure Parameters

Parameter Description

chain_name

The name of a chain to drop. Can also be a comma-delimited list of chains.

force

If force is set to FALSE, the chain must not be referenced by any job otherwise an error will occur.

If force is set to TRUE, all jobs pointing to the chain are disabled before dropping the chain.Running jobs that point to this chain will be stopped before the chain is dropped.


Usage Notes

Dropping a chain requires alter privileges on the chain (either by being the owner, or by having ALTER privileges on the chain or by having the CREATE ANY JOB system privilege).

All steps associated with the chain are dropped. If no rule set was specified when the chain was created, then the automatically created rule set and evaluation context associated with the chain are also dropped, so the user needs to have the privileges required to do this. See the DBMS_RULE_ADM.DROP_RULE_SET and DBMS_RULE_ADM.DROP_EVALUATION_CONTEXT procedures for more information.

If force is FALSE, no jobs may be using this chain. If force is TRUE, any jobs that use this chain will be disabled before dropping the chain (and any of these jobs that are running will be stopped).


DROP_CHAIN_RULE Procedure

This procedure removes a rule from an existing chain. The rule object corresponding to this rule will also be dropped. The chain will not be disabled. If dropping this rule makes the chain invalid, the user should first disable the chain to ensure that it does not run.

Syntax

DBMS_SCHEDULER.DROP_CHAIN_RULE (
   chain_name              IN VARCHAR2,
   rule_name               IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-34 DROP_CHAIN_RULE Procedure Parameters

Parameter Description

chain_name

The name of the chain to alter

rule_name

The name of the rule to drop

force

If force is set to TRUE, the drop operation proceeds even if the chain is currently running. The running chain is not stopped or interrupted. If force is set to FALSE and the chain is running, an error is generated.


Usage Notes

Dropping a chain rule requires alter privileges on the chain (either by being the owner, or by having ALTER privileges on the chain or by having the CREATE ANY JOB system privilege).

Dropping a chain rule also drops the underlying rule database object so the user needs to have the privileges to drop this rule object. See the DBMS_RULE_ADM.DROP_RULE procedure for more information.


DROP_CHAIN_STEP Procedure

This procedure drops a chain step. If this chain step is still used in the chain rules, the chain will be disabled.

Syntax

DBMS_SCHEDULER.DROP_CHAIN_STEP (
   chain_name              IN VARCHAR2,
   step_name               IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-35 DROP_CHAIN_STEP Procedure Parameters

Parameter Description

chain_name

The name of the chain to alter

step_name

The name of the step being dropped. Can be a comma-separated list.

force

If force is set to TRUE, this succeeds even if this chain is currently running. The running chain will not be stopped or interrupted.If force is set to FALSE and this chain is currently running, an error is thrown.


Usage Notes

Dropping a chain step requires ALTER privileges on the chain (either by being the owner, or by having ALTER privileges on the chain or by having the CREATE ANY JOB system privilege).


DROP_CREDENTIAL Procedure

This procedure drops a credential.

Syntax

DBMS_SCHEDULER.DROP_CREDENTIAL (
   credential_name         IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-36 DROP_CREDENTIAL Procedure Parameters

Parameter Description

credential_name

The name of the credential being dropped. This can optionally be prefixed with a schema. This cannot be set to NULL.

force

If set to FALSE, the credential must not be referenced by any job or an error will occur. If set to TRUE, the credential is dropped whether or not there are jobs referencing it. Jobs that reference the credential will continue to point to a nonexistent credential and throw an error at runtime.


Usage Notes

Only the owner of a credential or a user with the CREATE ANY JOB system privilege may drop the credential.

Running jobs that point to the credential are not affected by this procedure and are allowed to continue.


DROP_JOB Procedure

This procedure drops a job or all jobs in a job class. It results in the job being removed from the job queue, its metadata being removed, and no longer being visible in the *_SCHEDULER_JOBS views. Therefore, no more runs of the job will be executed. Dropping a job also drops all argument values set for that job.

Syntax

DBMS_SCHEDULER.DROP_JOB (
   job_name                IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE,
   commit_semantics        IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-37 DROP_JOB Procedure Parameters

Parameter Description

job_name

The name of a job or job class. Can be a comma-delimited list. For a job class, the SYS schema should be specified.

If the name of a job class is specified, the jobs that belong to that job class are dropped, but the job class itself is not dropped.

force

If force is set to FALSE, and an instance of the job is running at the time of the call, the call results in an error.

If force is set to TRUE, the Scheduler first attempts to stop the running job instance (by issuing the STOP_JOB call with the force flag set to false), and then drops the job.

commit_semantics

The commit semantics. The following types are supported:

  • STOP_ON_FIRST_ERROR - this procedure returns on the first error and the previous drop operations that were successful are committed to disk. This is the default.

  • TRANSACTIONAL - this procedure returns on the first error and everything that happened before that error is rolled back. This type is not supported when force is set to TRUE.

  • ABSORB_ERRORS - this procedure tries to absorb any errors and attempts dropping the rest of the jobs and commits all the drops that were successful.


Usage Notes

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


DROP_JOB_CLASS Procedure

This procedure drops a job class. Dropping a job class means that all the metadata about the job class is removed from the database.

Syntax

DBMS_SCHEDULER.DROP_JOB_CLASS (
   job_class_name          IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-38 DROP_JOB_CLASS Procedure Parameters

Parameter Description

job_class_name

The name of the job class. Can be a comma-delimited list.

force

If force is set to FALSE, a class must not be referenced by any jobs to be dropped otherwise an error will occur.

If force is set to TRUE, jobs belonging to the class are disabled and their class is set to the default class. Only if this is successful will the class be dropped.

Running jobs that belong to the job class are not affected.


Usage Notes

Dropping a job class requires the MANAGE SCHEDULER system privilege.


DROP_PROGRAM Procedure

This procedure drops a program. Any arguments that pertain to the program are also dropped when the program is dropped.

Syntax

DBMS_SCHEDULER.DROP_PROGRAM (
   program_name            IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-39 DROP_PROGRAM Procedure Parameters

Parameter Description

program_name

The name of the program to be dropped. Can be a comma-delimited list.

force

If force is set to FALSE, the program must not be referenced by any job otherwise an error will occur.

If force is set to TRUE, all jobs referencing the program are disabled before dropping the program.

Running jobs that point to the program are not affected by the DROP_PROGRAM call, and are allowed to continue.


Usage Notes

Dropping a program requires that you be the owner of the program or have ALTER privileges on that program. You can also drop a program if you have the CREATE ANY JOB privilege.


DROP_PROGRAM_ARGUMENT Procedure

This procedure drops a program argument. An argument can be specified by either name (if one has been given) or position.

The procedure is overloaded.

Syntax

Drops a program argument by position:

DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            IN VARCHAR2,
   argument_position       IN PLS_INTEGER);

Drops a program argument by name:

DBMS_SCHEDULER.DROP_PROGRAM_ARGUMENT (
   program_name            IN VARCHAR2,
   argument_name           IN VARCHAR2);

Parameters

Table 114-40 DROP_PROGRAM_ARGUMENT Procedure Parameters

Parameter Description

program_name

The name of the program to be altered. A program with this name must exist.

argument_name

The name of the argument being dropped

argument_position

The position of the argument to be dropped


Usage Notes

Dropping a program argument requires that you be the owner of the program or have ALTER privileges on that program. You can also drop a program argument if you have the CREATE ANY JOB privilege.


DROP_SCHEDULE Procedure

This procedure drops a schedule.

Syntax

DBMS_SCHEDULER.DROP_SCHEDULE (
   schedule_name    IN VARCHAR2,
   force            IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-41 DROP_SCHEDULE Procedure Parameters

Parameter Description

schedule_name

The name of the schedule. Can be a comma-delimited list.

force

If force is set to FALSE, the schedule must not be referenced by any job or window otherwise an error will occur.

If force is set to TRUE, any jobs or windows that use this schedule will be disabled before the schedule is dropped

Running jobs and open windows that point to the schedule are not affected.


Usage Notes

You must be the owner of the schedule being dropped or have ALTER privileges for the schedule or the CREATE ANY JOB privilege.


DROP_WINDOW Procedure

This procedure drops a window. All metadata about the window is removed from the database. All references to the window are removed from window groups.

Syntax

DBMS_SCHEDULER.DROP_WINDOW (
   window_name             IN VARCHAR2,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-42 DROP_WINDOW Procedure Parameters

Parameter Description

window_name

The name of the window. Can be a comma-delimited list.

force

If force is set to FALSE, the window must be not be open or referenced by any job otherwise an error will occur.

If force is set to TRUE, the window will be dropped and those jobs that have the window as their schedule will be disabled. However, jobs that have a window group of which the dropped window was a member as their schedule will not be disabled. If the window is open then, the Scheduler attempts to first close the window and then drop it. When the window is closed, normal close window rules apply.

Running jobs that have the window as their schedule will be allowed to continue, unless the stop_on_window_close flag was set to TRUE for the job. If this is the case, the job will be stopped when the window is dropped.


Usage Notes

Dropping a window requires the MANAGE SCHEDULER privilege.


DROP_WINDOW_GROUP Procedure

This procedure drops a window group but not the windows that are members of this window group.

Syntax

DBMS_SCHEDULER.DROP_WINDOW_GROUP (
   group_name              IN VARCHAR2
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-43 DROP_WINDOW_GROUP Procedure Parameters

Parameter Description

group_name

The name of the window group

force

If force is set to FALSE, the window group must not be referenced by any job otherwise an error will occur.

If force is set to TRUE, the window group will be dropped and those jobs that have the window group as their schedule will be disabled. Running jobs that have the window group as their schedule are allowed to continue, even if the stop_on_window_close flag was set to TRUE when for the job.

If a member of the window group that is being dropped is open, the window group can still be dropped.


Usage Notes

If you want to drop all the windows that are members of this group but not the window group itself, you can use the DROP_WINDOW procedure and provide the name of the window group to the call.

To drop a window group, you must have the MANAGE SCHEDULER privilege.


ENABLE Procedure

This procedure enables a program, job, chain, window, or window group. When an object is enabled, the enabled flag is set to TRUE. By default, jobs, chains, and programs are created disabled and windows and window groups are created enabled.

If a job was disabled and you enable it, the Scheduler begins to automatically run the job according to its schedule.

Validity checks are performed before enabling an object. If the check fails, the object is not enabled, and an appropriate error is returned. This procedure does not return an error if the object was already enabled.

Syntax

DBMS_SCHEDULER.ENABLE (
   name              IN VARCHAR2,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-44 ENABLE Procedure Parameters

Parameter Description

name

The name of the Scheduler object being enabled. Can be a comma-delimited list of names.

If a job class name is specified, then all the jobs in the job class are enabled.

If a window group name is specified, then the window group will be enabled, but the windows that are members of the window group, will not be enabled.

commit_semantics

The commit semantics. The following types are supported:

  • STOP_ON_FIRST_ERROR - The procedure returns on the first error and the previous enable operations that were successful are committed to disk. This is the default.

  • TRANSACTIONAL - This value permitted only when enabling jobs. The procedure returns on the first error and everything that happened before that error is rolled back. This type is only supported when enabling a job or a list of jobs.

  • ABSORB_ERRORS - This value permitted only when enablng jobs. The procedure tries to absorb any errors and attempts enabling the rest of the jobs and commits all the enable operations that were successful. This type is only supported when enabling a job or a list of jobs.


Usage Notes

Because the ENABLE procedure is used for several Scheduler objects, when enabling windows or window groups, they must be preceded by SYS.

To run ENABLE for a window or window group, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being enabled or have ALTER privileges on that object or have the CREATE ANY JOB privilege. 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.


END_DETACHED_JOB_RUN Procedure

This procedure ends a detached job run. A detached job points to a detached program, which is a program with the detached attribute set to TRUE. A detached job run does not end until this procedure or the STOP_JOB Procedure is called.

Syntax

DBMS_SCHEDULER.END_DETACHED_JOB_RUN (
   job_name          IN VARCHAR2,
   error_number      IN PLS_INTEGER DEFAULT 0,
   additional_info   IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-45 END_DETACHED_JOB_RUN Procedure Parameters

Parameter Description

job_name

The name of the job to end. Must be a detached job that is running.

error_number

If zero, then the job run is logged as succeeded. If non-zero, then the job run is logged as failed with this error number. If -1013, then the job run is logged as stopped.

additional_info

This text is stored in the additional_info column of the *_scheduler_job_run_details views for this job run.


Usage Notes

This procedure requires that you be the owner of the job or have ALTER privileges on the job. You can also end any detached job run if you have the CREATE ANY JOB privilege.

See Also:

Oracle Database Administrator's Guide for information about detached jobs.

EVALUATE_CALENDAR_STRING Procedure

You can define repeat intervals of jobs, windows or schedules using the Scheduler's calendaring syntax. This procedure evaluates the calendar expression and tells you what the next execution date and time of a job or window will be. This is very useful for testing the correct definition of the calendar string without having to actually schedule the job or window.

This procedure can also be used to get multiple steps of the repeat interval by passing the next_run_date returned by one invocation as the return_date_after argument of the next invocation of this procedure.

Syntax

DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING (
   calendar_string    IN  VARCHAR2,
   start_date         IN  TIMESTAMP WITH TIME ZONE,
   return_date_after  IN  TIMESTAMP WITH TIME ZONE,
   next_run_date      OUT TIMESTAMP WITH TIME ZONE);

Parameters

Table 114-46 EVALUATE_CALENDAR_STRING Procedure Parameters

Parameter Description

calendar_string

The calendar string to be evaluated. The string must be in the calendaring syntax described in "Operational Notes".

start_date

The date and time after which the repeat interval becomes valid. It can also be used to fill in specific items that are missing from the calendar string. Can optionally be NULL.

return_date_after

With the start_date and the calendar string, the Scheduler has sufficient information to determine all valid execution dates. By setting this argument, the Scheduler knows which one of all possible matches to return. When a NULL value is passed for this argument, the Scheduler automatically fills in systimestamp as its value.

next_run_date

The first timestamp that matches the calendar string and start date that occurs after the value passed in for the return_date_after argument.


Examples

The following code fragment can be used to determine the next five dates a job will run given a specific calendar string.

SET SERVEROUTPUT ON;
ALTER SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
Session altered.

DECLARE
start_date        TIMESTAMP;
return_date_after TIMESTAMP;
next_run_date     TIMESTAMP;
BEGIN
start_date :=
  to_timestamp_tz('01-JAN-2003 10:00:00','DD-MON-YYYY HH24:MI:SS');
return_date_after := start_date;
FOR i IN 1..5 LOOP
  DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING(  
    'FREQ=DAILY;BYHOUR=9;BYMINUTE=30;BYDAY=MON,TUE,WED,THU,FRI',
    start_date, return_date_after, next_run_date);
DBMS_OUTPUT.PUT_LINE('next_run_date: ' || next_run_date);
return_date_after := next_run_date;
END LOOP;
END;
/

next_run_date: 02-JAN-03 09.30.00.000000 AM
next_run_date: 03-JAN-03 09.30.00.000000 AM
next_run_date: 06-JAN-03 09.30.00.000000 AM
next_run_date: 07-JAN-03 09.30.00.000000 AM
next_run_date: 08-JAN-03 09.30.00.000000 AM

PL/SQL procedure successfully completed.

Usage Notes

No specific Scheduler privileges are required.


EVALUATE_RUNNING_CHAIN Procedure

This procedure forces reevaluation of the rules of a running chain to trigger any rules for which the conditions have been satisfied. The job passed as an argument must point to a chain and must be running. If the job is not running, an error is thrown. (RUN_JOB can be used to start the job.)

If any of the steps of the chain are themselves running chains, another EVALUATE_RUNNING_CHAIN is performed on each of the nested running chains.

Syntax

DBMS_SCHEDULER.EVALUATE_RUNNING_CHAIN (
   job_name              IN VARCHAR2);

Parameters

Table 114-47 EVALUATE_RUNNING_CHAIN Procedure Parameter

Parameter Description

job_name

The name of the running job (pointing to a chain) to reevaluate the rules for


Usage Notes

Running EVALUATE_RUNNING_CHAIN on a job requires alter privileges on the job (either by being the owner, or by having ALTER privileges on the job or by having the CREATE ANY JOB system privilege).

Note:

The Scheduler automatically evaluates a chain:
  • At the start of the chain job

  • When a chain step completes

  • When an event occurs that is associated with one of the event steps of the chain

For most chains, this is sufficient. EVALUATE_RUNNING_CHAIN should be used only under the following circumstances:

  • After manual intervention of a running chain with the ALTER_RUNNING_CHAIN procedure

  • When chain rules use SQL syntax and the rule conditions contain elements that are not under the control of the Scheduler.

In these cases, EVALUATE_RUNNING_CHAIN may not be needed if you set the evaluation_interval attribute when you created the chain.


GENERATE_JOB_NAME Function

This function returns a unique name for a job. The name will be of the form {prefix}N where N is a number from a sequence. If no prefix is specified, the generated name will, by default, be JOB$_1, JOB$_2, JOB$_3, and so on. If 'SCOTT' is specified as the prefix, the name will be SCOTT1, SCOTT2, and so on.

Syntax

DBMS_SCHEDULER.GENERATE_JOB_NAME (
   prefix        IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2;

Parameters

Table 114-48 GENERATE_JOB_NAME Function Parameter

Parameter Description

prefix

The prefix to use when generating the job name


Usage Notes

If the prefix is explicitly set to NULL, the name will be just the sequence number. In order to successfully use such numeric names, they must be surrounded by double quotes throughout the DBMS_SCHEDULER calls. A prefix cannot be longer than 18 characters and cannot end with a digit.

Note that, even though the GENERATE_JOB_NAME function will never return the same job name twice, there is a small chance that the returned name matches an already existing database object.

No specific Scheduler privileges are required to use this function.


GET_ATTRIBUTE Procedure

This procedure retrieves the value of an attribute of a Scheduler object. It is overloaded to output values of the following types: VARCHAR2, TIMESTAMP WITH TIMEZONE, BOOLEAN, PLS_INTEGER, and INTERVAL DAY TO SECOND.

Syntax

DBMS_SCHEDULER.GET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          OUT {VARCHAR2|TIMESTAMP WITH TIMEZONE| 
                     PLS_INTEGER|BOOLEAN|INTERVAL DAY TO SECOND});

DBMS_SCHEDULER.GET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          OUT {VARCHAR2|TIMESTAMP WITH TIMEZONE| 
                     PLS_INTEGER|BOOLEAN|INTERVAL DAY TO SECOND});
   value2         OUT VARCHAR2);

Parameters

Table 114-49 GET_ATTRIBUTE Procedure Parameters

Parameter Description

name

The name of the object

attribute

The attribute being retrieved

value

The existing value of the attribute

value2

Most attributes have only one value associated with them, but some can have two. The value2 argument is for this optional second value.


Usage Notes

To run GET_ATTRIBUTE for a job class, you must have the MANAGE SCHEDULER privilege or have EXECUTE privileges on the class. For a schedule, window, or a window group, no privileges are necessary. Otherwise, you must be the owner of the object or have ALTER or EXECUTE privileges on that object or have the CREATE ANY JOB privilege.


GET_FILE Procedure

This procedure retrieves a file from the operating system file system of a specified host. The file is copied to a destination, or its contents are returned in a procedure output parameter.

This procedures differs from the equivalent UTL_FILE procedure in that it uses a credential and can retrieve files from remote hosts that have only a Scheduler agent (and not an Oracle Database) installed.

You can also use this procedure to retrieve the standard output or error text for a run of an external job that has an associated credential.

Syntax

DBMS_SCHEDULER.GET_FILE (
   source_file                  IN VARCHAR2,
   source_host                  IN VARCHAR2,
   credential_name              IN VARCHAR2,
   file_contents                OUT {BLOB|CLOB});
DBMS_SCHEDULER.GET_FILE (
   source_file                  IN VARCHAR2,
   source_host                  IN VARCHAR2,
   credential_name              IN VARCHAR2,
   destination_file_name        IN VARCHAR2,
   destination_directory_object IN VARCHAR2,
   destination_permissions      IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-50 GET_FILE Procedure Parameters

Parameter Description

source_file

Fully qualified pathname of the file to retrieve from the operating system. The file name is case sensitive and is not converted to uppercase. If the file name starts with a question mark ('?'), the question mark is replaced by the path to the Oracle home if getting a file from the local host, or to the Scheduler agent home if getting a file from a remote host.

If the format of this parameter is external_log_id_stdout, then the stdout from the designated external job run is returned. If the format of this parameter is external_log_id_stderr, the error text from the designated external job run is returned. You obtain the value of external_log_id from the ADDITIONAL_INFO column of the *_SCHEDULER_JOB_RUN_DETAILS views. This column contains a set of name/value pairs in an indeterminate order, so you must parse this column for the external_log_id name/value pair, and then append either "_stdout" or "_stderr" to its value.

The external job must have an associated credential. The credential_name parameter of GET_FILE must name the same credential that is used by the job, and the source_host parameter must be the same as the destination attribute of the job.

source_host

If the file is to be retrieved from a remote host, then the host:port of the Scheduler agent must be specified. If source_host is NULL or set to 'localhost', then the file is retrieved from the file system of the local host. To determine the port number of a Scheduler agent, view the schagent.conf file, which is located in the Scheduler agent home directory on the remote host.

credential_name

The name of the credential to use for accessing the file system.

file_contents

The contents of the file will be returned in this variable.

destination_file_name

The contents of the file will be written into this file in the specified directory object.

destination_directory_object

The contents of the file will be written into the directory specified by this directory object. The caller must have the necessary privileges on the directory object.

destination_permissions

Reserved for future use


Usage Notes

The caller must have the CREATE EXTERNAL JOB system privilege and have EXECUTE privileges on the credential.


GET_SCHEDULER_ATTRIBUTE Procedure

This procedure retrieves the value of a Scheduler attribute.

Syntax

DBMS_SCHEDULER.GET_SCHEDULER_ATTRIBUTE (
   attribute      IN VARCHAR2,
   value          OUT VARCHAR2);

Parameters

Table 114-51 GET_SCHEDULER_ATTRIBUTE Procedure Parameters

Parameter Description

attribute

The name of the attribute

value

The existing value of the attribute


Usage Notes

To run GET_SCHEDULER_ATTRIBUTE, you must have the MANAGE SCHEDULER privilege.

Table 114-52 lists the Scheduler attributes that you can retrieve. For more detail on these attributes, see Table 114-75 and the section "Configuring the Scheduler" in Oracle Database Administrator's Guide.

Table 114-52 Scheduler Attributes Retrievable with GET_SCHEDULER_ATTRIBUTE

Scheduler Attribute Description

default_timezone

Default time zone used by the Scheduler for repeat intervals and windows

log_history

Retention period in days for job and window logs

max_job_slave_processes

Maximum number of job slave processes that the Scheduler can start. May be NULL.

current_open_window

Name of the currently open window

event_expiry_time

Time in seconds before an event generated by the Scheduler and enqueued onto the Scheduler event queue expires. May be NULL.



OPEN_WINDOW Procedure

This procedure opens a window independent of its schedule. This window will open and the resource plan associated with it, will take effect immediately for the duration specified or for the normal duration of the window if no duration is given. Only an enabled window can be manually opened.

Syntax

DBMS_SCHEDULER.OPEN_WINDOW (
   window_name             IN VARCHAR2,
   duration                IN INTERVAL DAY TO SECOND,
   force                   IN BOOLEAN DEFAULT FALSE);

Parameters

Table 114-53 OPEN_WINDOW Procedure Parameters

Parameter Description

window_name

The name of the window

duration

The duration of the window. It is of type interval day to second. If it is NULL, then the window will be opened for the regular duration as specified in the window metadata.

force

If force is set to FALSE, opening an already open window, will generate an error.

If force is set to TRUE:

You can open a window that is already open. The window stays open for the duration specified in the call, from the time the OPEN_WINDOW command was issued. Consider an example to illustrate this. window1 was created with a duration of four hours. It has how been open for two hours. If at this point you reopen window1 using the OPEN_WINDOW call and do not specify a duration, then window1 will be open for another four hours because it was created with that duration. If you specified a duration of 30 minutes, the window will close in 30 minutes.

The Scheduler automatically closes any window that is open at that time, even if it has a higher priority. For the duration of this manually opened window, the Scheduler does not open any other scheduled windows even if they have a higher priority.


Usage Notes

If there are jobs running when the window opens, the resources allocated to them might change due to the switch in resource plan.

Opening a window manually has no impact on regular scheduled runs of the window. The next open time of the window is not updated, and will be as determined by the regular scheduled opening.

When a window that was manually opened closes, the rules about overlapping windows are applied to determine which other window should be opened at that time if any at all.

If a window fails to switch resource plans because the designated resource plan no longer exists or because resource plan switching by windows is disabled (for example, by using the ALTER SYSTEM statement with the force option), the failure to switch resource plans is recorded in the window log.

Opening a window requires the MANAGE SCHEDULER privilege.


PURGE_LOG Procedure

By default, the Scheduler automatically purges all rows in the job log and window log that are older than 30 days. The PURGE_LOG procedure is used to purge additional rows from the job and window log.

Rows in the job log table pertaining to the steps of a chain are purged only when the entry for the main chain job is purged (either manually or automatically).

Syntax

DBMS_SCHEDULER.PURGE_LOG (
   log_history             IN PLS_INTEGER  DEFAULT 0,
   which_log               IN VARCHAR2     DEFAULT 'JOB_AND_WINDOW_LOG',
   job_name                IN VARCHAR2     DEFAULT NULL);

Parameters

Table 114-54 PURGE_LOG Procedure Parameters

Parameter Description

log_history

This specifies how much history (in days) to keep. The valid range is 0 - 999. If set to 0, no history is kept.

which_log

This specifies which type of log. Valid values for which_log are job_log, window_log, and job_and_window_log.

job_name

This specifies which job-specific entries must be purged from the jog log. This can be a comma-delimited list of job names and job classes. Whenever job_name has a value other than NULL, the which_log argument implicitly includes the job log.


Usage Notes

This procedure requires the MANAGE SCHEDULER privilege.

Examples

The following will completely purge all rows from both the job log and the window log:

DBMS_SCHEDULER.PURGE_LOG();

The following will purge all rows from the window log that are older than 5 days:

DBMS_SCHEDULER.PURGE_LOG(5, 'window_log');

The following will purge all rows from the window log that are older than 1 day and all rows from the job log that are related to jobs in jobclass1 and that are older than 1 day:

DBMS_SCHEDULER.PURGE_LOG(1, 'job_and_window_log', 'sys.jobclass1');

PUT_FILE Procedure

This procedure saves a file to the operating system file system of a specified remote host or of the local computer. It differs from the equivalent UTL_FILE procedure in that it uses a credential and can save files to a remote host that has only a Scheduler agent (and not an Oracle Database) installed.

Syntax

DBMS_SCHEDULER.PUT_FILE (
   destination_file         IN VARCHAR2,
   destination_host         IN VARCHAR2,
   credential_name          IN VARCHAR2,
   file_contents            IN {BLOB|CLOB},
   destination_permissions  IN VARCHAR2 DEFAULT NULL);
DBMS_SCHEDULER.PUT_FILE (
   destination_file         IN VARCHAR2,
   destination_host         IN VARCHAR2,
   credential_name          IN VARCHAR2,
   source_file_name         IN VARCHAR2,
   source_directory_object  IN VARCHAR2,
   destination_permissions  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-55 PUT_FILE Procedure Parameters

Parameter Description

destination_file

Fully qualified pathname of the file to save to the operating system file system. The file name is case sensitive. If the file name starts with a question mark ('?'), the question mark is replaced by the path to the Oracle home if saving to the local host, or to the Scheduler agent home if saving to a remote host.

destination_host

If NULL or set to 'localhost', the file is saved to the file system of the local computer. For saving to a remote host, must contain the host:port of the remote host, where host is the host name or IP address of the remote host, and port is the port on which the remote Scheduler agent listens. To determine the port number of a remote Scheduler agent, view the schagent.conf file, which is located in the Scheduler agent home directory on the remote host.

credential_name

The name of the credential to use for accessing the destination file system.

file_contents

The contents of the file will be read from this variable.

source_file_name

The contents of the file will be read from this file.

source_directory_object

The directory object that specifies the path to the source file, when source_file_name is used. The caller must have the necessary privileges on the directory object.

destination_permissions

Reserved for future use


Usage Notes

The caller must have the CREATE EXTERNAL JOB system privilege and have EXECUTE privileges on the credential.


REMOVE_EVENT_QUEUE_SUBSCRIBER Procedure

This procedure unsubscribes a user from the Scheduler event queue SYS.SCHEDULER$_EVENT_QUEUE.

Syntax

DBMS_SCHEDULER.REMOVE_EVENT_QUEUE_SUBSCRIBER (
   subscriber_name         IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-56 REMOVE_EVENT_QUEUE_SUBSCRIBER Procedure Parameters

Parameter Description

subscriber_name

Name of the Oracle Streams Advanced Queuing (AQ) agent for which to remove the subscription. If NULL, the user name of the calling user is used.


Usage Notes

After the agent is unsubscribed, it is deleted. If the agent does not exist or is not currently subscribed to the Scheduler event queue, an error is raised.


REMOVE_WINDOW_GROUP_MEMBER Procedure

This procedure removes one or more windows from an existing window group.

Syntax

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

Parameters

Table 114-57 REMOVE_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 any of the windows specified is either invalid, does not exist, or is not a member of the given group, the call fails. Removing a window from a group requires the MANAGE SCHEDULER privilege.

Dropping an open window from a window group has no impact on running jobs that has the window as its schedule since the jobs would only be stopped when a window closes.


RESET_JOB_ARGUMENT_VALUE Procedure

This procedure resets (clears) the value previously set to an argument for a job.

RESET_JOB_ARGUMENT_VALUE is overloaded.

Syntax

Clears a previously set job argument value by argument position:

DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE (
   job_name                IN VARCHAR2,
   argument_position       IN PLS_INTEGER);

Clears a previously set job argument value by argument name:

DBMS_SCHEDULER.RESET_JOB_ARGUMENT_VALUE (
   job_name                IN VARCHAR2,
   argument_name           IN VARCHAR2);

Parameters

Table 114-58 RESET_JOB_ARGUMENT_VALUE Procedure Parameters

Parameter Description

job_name

The name of the job being altered

argument_position

The position of the program argument being reset

argument_name

The name of the program argument being reset


Usage Notes

If the corresponding program argument has no default value, the job will be disabled. Resetting a program argument of a job belonging to another user requires ALTER privileges on that job. Arguments can be specified by position or by name.

RESET_JOB_ARGUMENT_VALUE requires that you be the owner of the job or have ALTER privileges on that job. You can also reset a job argument value if you have the CREATE ANY JOB privilege.


RUN_CHAIN Procedure

This procedure immediately runs a chain or part of a chain by creating a run-once job with the job name given. If no job_name is given, one will be generated of the form RUN_CHAIN$_chainnameN, where chainname is the first 8 characters of the chain name and N is an integer.If a list of start steps is given, only those steps will be started when the chain begins running. Steps not in the list that would normally have started are skipped and paused (so that they or the steps after them do not run). If start_steps is NULL, then the chain will start normally—that is, an initial evaluation will be done to see which steps to start running).

If a list of initial step states is given, the newly created chain job sets every listed step to the state specified for that step before evaluating the chain rules to see which steps to start. (Steps in the list are not started.)

Syntax

Runs a chain, with a list of start steps.

DBMS_SCHEDULER.RUN_CHAIN (
   chain_name                IN VARCHAR2,
   start_steps               IN VARCHAR2,
   job_name                  IN VARCHAR2 DEFAULT NULL);

Runs a chain, with a list of initial step states.

DBMS_SCHEDULER.RUN_CHAIN (
   chain_name               IN VARCHAR2, 
   step_state_list          IN SYS.SCHEDULER$_STEP_TYPE_LIST, 
   job_name                 IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-59 RUN_CHAIN Procedure Parameters

Parameter Description

chain_name

The name of the chain to run

job_name

The name of the job to create to run the chain

start_steps

Comma-separated list of the steps to start when the chain starts running

step_state_list

List of chain steps with an initial state (SUCCEEDED or FAILED) to set for each.

Set the attributes of sys.scheduler$_step_type as follows:

step_name The name of the step step_type 'SUCCEEDED' or 'FAILED error_number'

where error_number is a positive or negative integer.


Usage Notes

Running a chain requires CREATE JOB if the job is being created in the user's schema, or CREATE ANY JOB otherwise. In addition, the owner of the job being created needs execute privileges on the chain (by being the owner of the chain, by having the EXECUTE privilege on the chain, or by having the EXECUTE ANY PROGRAM system privilege).

Examples

The following example illustrates how to start a chain in the middle by providing the initial state of some chain steps.

declare
  initial_step_states sys.scheduler$_step_type_list;
begin
  initial_step_states := sys.scheduler$_step_type_list(
    sys.scheduler$_step_type('step1', 'SUCCEEDED'),
    sys.scheduler$_step_type('step2', 'FAILED 27486'),
    sys.scheduler$_step_type('step3', 'SUCCEEDED'),
    sys.scheduler$_step_type('step5', 'SUCCEEDED'));
  dbms_scheduler.run_chain('my_chain', initial_step_states);
end;
/

RUN_JOB Procedure

This procedure runs a job immediately.

It is not necessary to call RUN_JOB to run a job according to its schedule. Provided that that job is enabled, the Scheduler runs it automatically. Use RUN_JOB to run a job outside of its normal schedule.

Syntax

DBMS_SCHEDULER.RUN_JOB (
   job_name                IN VARCHAR2,
   use_current_session     IN BOOLEAN DEFAULT TRUE);

Parameters

Table 114-60 RUN_JOB Procedure Parameters

Parameter Description

job_name

The name of the job being run

use_current_session

This specifies whether the job run should occur in the same session as the one that the procedure was invoked from.

When use_current_session is set to TRUE:

  • The job runs as the user who called RUN_JOB.

  • You can test a job and see any possible errors on the command line.

  • run_count, last_start_date, last_run_duration, and failure_count are not updated.

  • RUN_JOB can be run in parallel with a regularly scheduled job run.

When use_current_session is set to FALSE:

  • The job runs as the user who is the job owner.

  • You need to check the job log to find error information.

  • run_count, last_start_date, last_run_duration, and failure_count are updated.

  • RUN_JOB fails if a regularly scheduled job is running.


Usage Notes

The job does not have to be enabled. If the job is disabled, the following validity checks are performed before running it:

The job can be run in two different modes. One is in the current user session. In this case, the call to RUN_JOB will block until it has completed the job. Any errors that occur during the execution of the job will be returned as errors to the RUN_JOB procedure. The other option is to run the job immediately like a regular job. In this case, RUN_JOB returns immediately and the job will be picked up by the coordinator and passed on to a job slave for execution. The Scheduler views and logs must be queried for the outcome of the job.

Multiple user sessions can use RUN_JOB in their sessions simultaneously when use_current_session is set to TRUE.

When using RUN_JOB with jobs that point to chains, use_current_session must be FALSE.

RUN_JOB requires that you be the owner of the job or have ALTER privileges on that job. You can also run a job if you have the CREATE ANY JOB privilege.


SET_AGENT_REGISTRATION_PASS Procedure

This procedure sets the agent registration password for a database. A Scheduler agent must register with the database before the database can submit jobs to the agent. The agent must provide this password when registering.

Syntax

DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS (
   registration_password   IN VARCHAR2,
   expiration_date         IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   max_uses                IN NUMBER DEFAULT NULL);

Parameters

Table 114-61 SET_AGENT_REGISTRATION_PASS Procedure Parameters

Parameter Description

registration_password

This is the password that remote agents must specify in order to successfully register with the database. If this is NULL, then no agents will be able to register with the database.

expiration_date

If this is set to a non-NULL value, then the registration_password will not be valid after this date. After this date, no agents will be able to register with the database. This cannot be set to a date in the past.

max_uses

This is the maximum number of successful registrations that can be performed with this password. After the number of successful registrations has been performed with this password, then no agents will be able to register with the database. This cannot be set to 0 or a negative value. If this is set to NULL, then there will be no limit on the number of successful registrations.


Usage Notes

To prevent abuse, this password can be set to expire after a given date or a maximum number of successful registrations. This procedure will overwrite any password already set. This requires the MANAGE SCHEDULER system privilege.

By default, max_uses is set to NULL which means that there will be no limit on the number of successful registrations.

Oracle recommends that an agent registration password be reset after every agent registration or every known set of agent registrations. Furthermore, Oracle recommends that this password be set to NULL if no new agents are being registered.


SET_ATTRIBUTE Procedure

This procedure changes an attribute of an object. It is overloaded to accept values of the following types: VARCHAR2, TIMESTAMP WITH TIMEZONE, BOOLEAN, PLS_INTEGER, and INTERVAL DAY TO SECOND. To set an attribute to NULL, the SET_ATTRIBUTE_NULL procedure should be used. What attributes can be set depends on the object being altered. With the exception of the object name, all object attributes can be changed.

SET_ATTRIBUTE is overloaded.

Syntax

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN {VARCHAR2|TIMESTAMP WITH TIMEZONE|
                      PLS_INTEGER|BOOLEAN|INTERVAL DAY TO SECOND});

DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           IN VARCHAR2,
   attribute      IN VARCHAR2,
   value          IN {VARCHAR2|TIMESTAMP WITH TIMEZONE|
                      PLS_INTEGER|BOOLEAN|INTERVAL DAY TO SECOND});
   value2         IN VARCHAR2 DEFAULT NULL);

Parameters

Table 114-62 SET_ATTRIBUTE Procedure Parameters

Parameter Description

name

The name of the object

attribute

See Table 114-63 through Table 114-70.

value

The new value being set for the attribute. This cannot be NULL. To set an attribute value to NULL, use the SET_ATTRIBUTE_NULL procedure.

value2

Most attributes have only one value associated with them, but some can have two. The value2 argument is for this optional second value.


Usage Notes

If an object is altered and it was in the enabled state, the Scheduler will first disable it, make the change and then re-enable it. If any errors are encountered during the enable process, the object is not re-enabled and an error is generated.

If an object is altered and it was in the disabled state, it will remain disabled after it is altered.

To run SET_ATTRIBUTE for a window, window group, or job class, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being altered or have ALTER privileges on that object or have the CREATE ANY JOB privilege.

Job

If there is a running instance of the job when the SET_ATTRIBUTE call is made, it is not affected by the call. The change is only seen in future runs of the job.

If any of the schedule attributes of a job are altered while the job is running, the time of the next job run will be scheduled using the new schedule attributes. Schedule attributes of a job include schedule_name, start_date, end_date, and repeat_interval.

If any of the program attributes of a job are altered while the job is running, the new program attributes will take effect the next time the job runs. Program attributes of a job include program_name, job_action, job_type, and number_of_arguments. This is also the case for job argument values that have been set.

Granting ALTER on a job will let a user alter all attributes of that job except its program attributes (program_name, job_type, job_action, program_action, and number_of_arguments) and will not allow a user to use a PL/SQL expression to specify the schedule for a job.

Oracle recommends that you not alter a job that was automatically created for you by the database. Jobs that were created by the database have the column SYSTEM set to TRUE in job views.

Program

If any currently running jobs use the program that is altered, they will continue to run with the program definition prior to the alter. The job will run with the new program definition the next time the job executes.

Schedule

If a schedule is altered, the change will not affect running jobs and open windows that use this schedule. The change will only be in effect the next time the jobs runs or the window opens.

Job Class

With the exception of the default job class, all job classes can be altered. To alter a job class, you must have the MANAGE SCHEDULER privilege.

When a job class is altered, running jobs that belong to the class are not affected. The change only takes effect for jobs that have not started running yet.

Window

When a window is altered, it does not affect an active window. The changes only take effect the next time the window opens.

If there is no current resource plan when a window opens that designates a resource plan, the Resource Manager activates with that plan.

Job Attribute Values

Table 114-63 lists attribute values for jobs.

Note:

See the CREATE_JOB procedure and the CREATE_JOBS procedure for more complete descriptions of the attributes in this table.

Table 114-63 Job Attribute Values

Name Description

auto_drop

This attribute, if TRUE, causes a job to be automatically dropped after it completes or is automatically 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 automatically disabled when it has failed max_failures times. max_failures is also set with SET_ATTRIBUTE.

If this attribute 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.

comments

An optional comment.

credential_name

This attribute specifies the credential to use when running an external job. If this attribute is NULL (the default) and destination is NULL, then a preferred (default) credential is selected. A remote external job must have a non-NULL credential_name. See Oracle Database Administrator's Guide for information about preferred credentials for local external jobs.

database_role

This attribute applies when the database participates in an Oracle Data Guard environment. If this attribute is set to 'PRIMARY', the job runs only when the database is in the role of the primary database. If set to 'LOGICAL STANDBY', the job runs only when the database is in the role of a logical standby. The default is 'PRIMARY' when the database is the primary database, and 'LOGICAL STANDBY' when the database is a logical standby.

Note: If you want a job to run for all database roles on a particular host, you must create two copies of the job on that host: one with a database_role of 'PRIMARY', and the other with a database_role of 'LOGICAL STANDBY'.

destination

This attribute specifies a host and port on which to run a remote external job. The attribute is of the form host:port, where host is the host name or IP address of the destination host, and port is the port number on which the Scheduler agent on that host listens. This attribute is set to NULL by default.

end_date

Specifies the date and time after which the job expires and is no longer run. After the end_date, if auto_drop is TRUE, the job is dropped. If auto_drop is FALSE, the job is disabled and the STATE of the job is set to COMPLETED.

If no value for end_date is specified, the job repeats 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.

event_spec

This attribute takes two values: the value argument should contain the event condition and the value2 argument should contain the queue specification. For details on what these arguments should contain, see the descriptions for the event_condition and queue_spec arguments in the CREATE_JOB procedure.

follow_default_timezone

If TRUE and if the job start_date is null, then when the default_timezone scheduler attribute is changed, the Scheduler recomputes the next run date and time for this job so that it is in accordance with the new time zone. For example, if the job was set to run at 02:00 in the previous time zone, it will run at 02:00 in the new time zone. (If the job start_date is not null, then the time zone for the run date and time for the job is always specified by the time zone of the start_date.) If FALSE, the next start date and time for the job is not recomputed when the default_timezone scheduler attribute is changed. In this case, if the old time zone is three hours earlier than the new time zone, then a job scheduled to run at 02:00 in the old time zone runs at 05:00 in the new time zone.

(Summer/winter transitions do not change the default time zone name.)

instance_id

Valid only in an Oracle Real Application Clusters environment. Indicates the instance on which the job is to be run.

instance_stickiness

This attribute should only be used for a database running in an Oracle Real Application Clusters (RAC) environment. By default, it is set to TRUE. If you set instance_stickiness to TRUE, jobs start running on the instance with the lightest load and the Scheduler thereafter attempts to run on the instance that it last ran on. If that instance is either down or so overloaded that it will not start new jobs for a significant period of time, another instance will run the job. If the interval between runs is large, instance_stickiness will be ignored an the job will be handled as if it were a non-sticky job.

If instance_stickiness is set to FALSE, each instance of the job runs on the first instance available.

For non-RAC environments, this attribute is not useful because there is only one instance.

job_action

The action that the job performs, depending on the job_type attribute. For example, if job_type is 'STORED_PROCEDURE', job_action contains the name of the stored procedure.

job_class

The class this job is associated with.

job_priority

This attribute specifies the priority of this job relative to other jobs in the same class as this job. If multiple jobs within a class are scheduled to be executed at the same time, the job priority determines the order in which jobs from that class are picked up for execution by the job coordinator. It can be a value from 1 through 5, with 1 being the first to be picked up for job execution.

If no job priority is specified when creating a job, the default priority of 3 is assigned to it.

job_type

The type of this job. Can be any of: 'PLSQL_BLOCK', 'STORED_PROCEDURE', 'EXECUTABLE', and 'CHAIN'.

If this is set, program_name must be NULL.

job_weight

This attribute is for expert users of parallel technology only. If your job will be using parallel technology, you can set the value of this attribute to the degree of parallelism of your SQL inside the job.

job_weight has a range of 1-100, with 1 being the default

logging_level

This attribute specifies how much information is logged. The possible options are:

DBMS_SCHEDULER.LOGGING_OFF

(The default) No logging is performed for this job. However, the logging level of the job class takes precedence and job logging may occur.

DBMS_SCHEDULER.LOGGING_FAILED_RUNS

The Scheduler logs only jobs that failed, with the reason for failure. If the job class has a higher logging level, then the higher logging level takes precedence.

DBMS_SCHEDULER.LOGGING_RUNS

The Scheduler writes detailed information to the job log for all runs of each job in this class. If the job class has a higher logging level, then the higher logging level takes precedence.

DBMS_SCHEDULER.LOGGING_FULL

In addition to recording every run of a job, the Scheduler records all operations performed on the job, including create, enable, disable, alter (with SET_ATTRIBUTE), stop, and so on.

max_failures

This attribute specifies the number of times a job can fail on consecutive scheduled runs before it is automatically disabled. Once a job is disabled, it is no longer executed and its STATE is set to BROKEN in the *_SCHEDULER_JOB views.

max_failures can be an integer between 1 to 1,000,000. By default, it is set to NULL, which indicates that new instances of the job will be started regardless of how many previous instances have failed.

max_run_duration

This attribute specifies the maximum amount of time that the job should be allowed to run. Its datatype is INTERVAL DAY TO SECOND. If this attribute is set to a non-zero and non-NULL value, and job duration exceeds this value, the Scheduler raises an event of type JOB_OVER_MAX_DUR. It is then up to your event handler to decide whether or not to allow the job to continue.

max_runs

This attribute specifies the maximum number of consecutive scheduled runs of the job. Once max_runs is reached, the job is disabled and its state is changed to COMPLETED.

max_runs can be an integer between 1 and 1,000,000. By default, it is set to NULL, which means that it will repeat forever or until end_date or max_failures is reached.

number_of_arguments

The number of arguments if the program is inlined. If this is set, program_name should be NULL.

parallel_instances

This is a Boolean attribute. If set to TRUE and an event is raised, then a run-once lightweight job will be created to handle the event; the job will be dropped once it has completed running.

This lightweight job will not be visible in any of the *_SCHEDULER_JOBS views - it will be visible in the *_SCHEDULER_RUNNING_JOBS views. The name of this lightweight job will be the same as that of the parent job and a subname will be automatically generated to distinguish it from its parent and from its siblings.

program_name

The name of a program object to use with this job. If this is set, job_action, job_type and number_of_arguments should be NULL.

raise_events

This attribute tells the Scheduler at what stages of the job's execution events should be raised. It is a bit vector in which zero or more of the following bits can be set. Each bit has a package constant corresponding to it.

  • job_started CONSTANT PLS_INTEGER := 1

  • job_succeeded CONSTANT PLS_INTEGER := 2

  • job_failed CONSTANT PLS_INTEGER :=4

  • job_broken CONSTANT PLS_INTEGER :=8

  • job_completed CONSTANT PLS_INTEGER :=16

  • job_stopped CONSTANT PLS_INTEGER :=32

  • job_sch_lim_reached CONSTANT PLS_INTEGER :=64

  • job_disabled CONSTANT PLS_INTEGER :=128

  • job_chain_stalled CONSTANT PLS_INTEGER :=256

  • job_all_events CONSTANT PLS_INTEGER := 511

  • job_run_completed CONSTANT PLS_INTEGER := job_succeeded + job_failed + job_stopped

Table 114-64 describes these event types in detail.

repeat_interval

Either a PL/SQL function returning the next date and time on which to run, or calendaring syntax expression. If this is set, schedule_name should be NULL. See "Calendaring Syntax" for more information.

restartable

This attribute specifies whether a job can be restarted in case of failure. By default, jobs are not restartable and this attribute is set to FALSE. Setting this to TRUE means that if a job fails while running, it will be restarted from the beginning point of the job.

In the case of a chain job, if this attribute is TRUE, the chain is restarted from the beginning after an application failure. If this attribute is FALSE, or if there has been a database failure, the chain is restarted at the last running step. The restart_on_recovery attribute of that step then determines if the step is restarted or marked as stopped. (If marked as stopped, the chain evaluates rules and continues.)

Note that setting this attribute to TRUE might lead to data inconsistencies in some situations, for example, if data is committed within a job.

Retries on errors are not counted as regular runs. The run count or failure count is not incremented until the job succeeds or has failed all its six retries.

The restartable attribute is used by the Scheduler to determine whether to retry the job not only on regular application errors, but after a database malfunction as well. The Scheduler will retry the job a maximum of six times. The first time, it will wait for one second and multiply this wait time with a factor of 10 each time thereafter.

Both the run count and failure count are incremented by 1 if the job has failed all its six retries. If the job immediately succeeds, or it succeeds on one of its retries, run count is incremented by 1.

The Scheduler will stop retrying a job when:

  • One of the retries succeeds.

  • All of its six retries have failed.

  • The next retry would occur after the next regularly scheduled run of the job.

The Scheduler no longer retries the job if the next scheduled retry is past the next regularly scheduled run for repeating jobs.

schedule_limit

In heavily loaded systems, jobs are not always started at their scheduled time. This attribute enables you to have the Scheduler not start a job at all if the delay in starting the job is larger than the interval specified. It can be a value of 1 minute to 99 days. For example, if a job was supposed to start at noon and the schedule limit is set to 60 minutes, the job will not be run if it has not started to run by 1:00 p.m.

If schedule_limit is not specified, the job is executed at some later date as soon as there are resources available to run it. By default, this attribute is set to null, which indicates that the job can be run at any time after its scheduled time. A scheduled job run that is skipped because of this attribute does not count against the number of runs and failures of the job. An entry in the job log will be made to reflect the skipped run.

schedule_name

The name of a schedule or window or window group to use as the schedule for this job. If this is set, end_date, start_date and repeat_interval should all be NULL.

start_date

The original date and time on which this job started or will be scheduled to start. If this is set, schedule_name should be NULL.

stop_on_window_close

This attribute only applies if the schedule of a job is a window or a window group. Setting this attribute to TRUE implies that the job should be stopped once the associated window is closed. The job is stopped using the stop_job procedure with force set to FALSE.

By default, stop_on_window_close is set to FALSE. Therefore, if you do not set this attribute, the job will be allowed to continue after the window closes.

Note that, although the job is allowed to continue, its resource allocation will probably change because closing a window generally also implies a change in resource plans.


Table 114-64 Event Types Raised by the Scheduler

Event Type Description

job_all_events

Not an event, but a constant that provides an easy way for you to enable all events

job_broken

The job has been disabled and has changed to the BROKEN state because it exceeded the number of failures defined by the max_failures job attribute

job_chain_stalled

A job running a chain was put into the CHAIN_STALLED state. A running chain becomes stalled if there are no steps running or scheduled to run and the chain evaluation_interval is set to NULL. No progress will be made in the chain unless there is manual intervention.

job_completed

The job completed because it reached its max_runs or end_date

job_disabled

The job was disabled by the Scheduler or by a call to SET_ATTRIBUTE

job_failed

The job failed, either by throwing an error or by abnormally terminating

job_run_completed

A job run either failed, succeeded, or was stopped

job_sch_lim_reached

The job's schedule limit was reached. The job was not started because the delay in starting the job exceeded the value of the schedule_limit job attribute.

job_started

The job started

job_stopped

The job was stopped by a call to STOP_JOB

job_succeeded

The job completed successfully


Program Attribute Values

Table 114-65 lists program attribute values.

Note:

See the CREATE_PROGRAM procedure for more complete descriptions of the attributes in this table.

Table 114-65 Program Attribute Values

Name Description

comments

An optional comment. This can describe what the program does, or give usage details.

detached

If TRUE, the program is a detached program. See Oracle Database Administrator's Guide for information about detached jobs and detached programs.

number_of_arguments

The number of arguments required by the stored procedure or other executable that the program invokes

program_action

The action that the program performs, depending on the program_type attribute. For example, if program_type is 'STORED_PROCEDURE', program_action contains the name of the stored procedure.

program_type

The type of program. This must be one of the following supported program types: 'PLSQL_BLOCK', 'STORED_PROCEDURE', and 'EXECUTABLE'.


Job Class Attribute Values

Table 114-66 lists job class attribute values.

Note:

See the CREATE_JOB_CLASS procedure for more complete descriptions of the attributes in this table.

Table 114-66 Job Class Attribute Values

Name Description

comments

An optional comment about the class.

log_history

This attribute controls the number of days that job log entries for jobs in this class are retained. It helps prevent the job log from growing indiscriminately.

The range of valid values is 0 through 999. If set to 0, no history is kept. If NULL, retention days are set by the log_history Scheduler attribute (set with SET_SCHEDULER_ATTRIBUTE).

logging_level

This attribute specifies how much information is logged. The possible options are:

  • DBMS_SCHEDULER.LOGGING_OFF

    No logging will be performed for any jobs in this class.

  • DBMS_SCHEDULER.LOGGING_FAILED_RUNS

    The Scheduler logs only jobs in the class that failed, with the reason for failure.

  • DBMS_SCHEDULER.LOGGING_RUNS

    The Scheduler writes 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 records all operations performed on all jobs in this class. Every time a job is created, enabled, disabled, altered (with SET_ATTRIBUTE), stopped, and so on, an entry is recorded in the log.

resource_consumer_group

The resource consumer group that a class is associated with. All jobs in the class run under this resource consumer group. See Oracle Database Administrator's Guide for a description of resource consumer groups and the Database Resource Manager.

service

The database service that the jobs in the job class have affinity to. If both the resource_consumer_group and service attributes are set for a job class, and if the service is mapped to a resource consumer group, the resource_consumer_group attribute takes precedence.


Window Attribute Values

Table 114-67 lists window attribute values.

Note:

See the CREATE_WINDOW procedure for more complete descriptions of the attributes in this table.

Table 114-67 Window Attribute Values

Name Description

comments

An optional comment about the window.

duration

The duration of the window.

end_date

The date after which the window will no longer open. If this is set, schedule_name must be NULL.

repeat_interval

A string using the calendaring syntax. PL/SQL date functions are not allowed. If this is set, schedule_name must be NULL. See "Calendaring Syntax" for more information.

resource_plan

The resource plan to be associated with a window. When the window opens, the system will switch to this resource plan. When the window closes, the original resource plan will be restored. If a resource plan has been made active with the force option, no resource plan switch will occur.

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.

schedule_name

The name of a schedule to use with this window. If this is set, start_date, end_date, and repeat_interval must all be NULL.

start_date

The next date and time on which this window is scheduled to open. If this is set, schedule_name must be NULL.

window_priority

The priority of the window. Must be one of 'LOW' (default) or 'HIGH'.


Window Group Attribute Values

Table 114-68 lists window group attribute values.

Table 114-68 Window Group Attribute Values

Name Description

comments

An optional comment about the window group.


Schedule Attribute Values

Table 114-69 lists schedule attribute values.

Note:

See the CREATE_SCHEDULE and CREATE_CALENDAR_SCHEDULE procedures for more complete descriptions of the attributes in this table.

Table 114-69 Schedule Attribute Values

Name Description

comments

An optional comment.

end_date

The cutoff date and time after which the schedule will not specify any dates.

event_spec

This attribute takes two values: the value argument should contain the event condition and the value2 argument should contain the queue specification. For details on what these arguments should contain, see the descriptions for the event_condition and queue_spec arguments to the CREATE_JOB procedure.

repeat_interval

An expression using the calendaring syntax. See "Calendaring Syntax" for more information.

source

The host name that the database is running on. This is used by remote agents to know where to send the job run details.

start_date

The start or reference date and time used by the calendaring syntax.


Chain Attribute Values

Table 114-70 lists chain attribute values.

Note:

See the CREATE_CHAIN procedure for more complete descriptions of the attributes in this table.

Table 114-70 Chain Attribute Values

Name Description

comments

An optional comment describing the purpose of the chain.

evaluation_interval

If this is not NULL, evaluation of the chain occurs not only at normal evaluation times (when the job starts, when a step completes, or when an event that is associated with an event step arrives), but also periodically at this interval.

For most chains, the normal evaluation times are sufficient. Because evaluation of a large chain is CPU intensive, this attribute should be used only when chain rules use SQL syntax and the rule conditions contain elements that are not under the control of the Scheduler.



SET_ATTRIBUTE_NULL Procedure

This procedure sets an attribute of an object to NULL. What attributes can be set depends on the object being altered. If the object is enabled, it will be disabled before being altered and be reenabled afterward. If the object cannot be reenabled, an error is generated and the object will be left in a disabled state.

Syntax

DBMS_SCHEDULER.SET_ATTRIBUTE_NULL (
   name              IN VARCHAR2,
   attribute         IN VARCHAR2);

Parameters

Table 114-71 SET_ATTRIBUTE_NULL Procedure Parameters

Parameter Description

name

The name of the object

attribute

The attribute being changed


Usage Notes

To run SET_ATTRIBUTE_NULL for a window, window group, or job class, you must have the MANAGE SCHEDULER privilege. Otherwise, you must be the owner of the object being altered or have ALTER privileges on that object or have the CREATE ANY JOB privilege.


SET_JOB_ANYDATA_VALUE Procedure

This procedure sets the value for an argument of the associated program for a job, encapsulated in an AnyData object. It overrides any default value set for the program argument. NULL is a valid assignment for a program argument. The argument can be specified by position or by name. You can specify by name only when:

No type checking of the argument is done at any time by the Scheduler.

SET_JOB_ANYDATA_VALUE is overloaded.

Syntax

Sets a program argument by its position.

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
   job_name                IN VARCHAR2,
   argument_position       IN PLS_INTEGER,
   argument_value          IN SYS.ANYDATA);

Sets a program argument by its name.

DBMS_SCHEDULER.SET_JOB_ANYDATA_VALUE (
   job_name                IN VARCHAR2,
   argument_name           IN VARCHAR2,
   argument_value          IN SYS.ANYDATA);

Parameters

Table 114-72 SET_JOB_ANYDATA_VALUE Procedure Parameters

Parameter Description

job_name

The name of the job to be altered

argument_name

The name of the program argument being set

argument_position

The position of the program argument being set

argument_value

The new value to be assigned to the program argument, encapsulated in an AnyData object


Usage Notes

SET_JOB_ANYDATA_VALUE requires that you be the owner of the job or have ALTER privileges on that job. You can also set a job argument value if you have the CREATE ANY JOB privilege.

SET_JOB_ANYDATA_VALUE does not apply to lightweight jobs because lightweight jobs cannot take AnyData arguments.


SET_JOB_ARGUMENT_VALUE Procedure

This procedure sets the value of an argument of the associated program for a job. It overrides any default value set for the program argument. NULL is a valid assignment for a program argument. The argument can be specified by position or by name. You can specify by name only when:

No type checking of the argument is done at any time by the Scheduler.

SET_JOB_ARGUMENT_VALUE is overloaded.

Syntax

Sets an argument value by position:

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                IN VARCHAR2,
   argument_position       IN PLS_INTEGER,
   argument_value          IN VARCHAR2);

Sets an argument value by name:

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
   job_name                IN VARCHAR2,
   argument_name           IN VARCHAR2,
   argument_value          IN VARCHAR2);

Parameters

Table 114-73 SET_JOB_ARGUMENT_VALUE Procedure Parameters

Parameter Description

job_name

The name of the job to be altered

argument_name

The name of the program argument being set

argument_position

The position of the program argument being set

argument_value

The new value to be set for the program argument. To set a non-VARCHAR value, use the SET_JOB_ANYDATA_ARGUMENT_VALUE procedure.


Usage Notes

SET_JOB_ARGUMENT_VALUE requires that you be the owner of the job or have ALTER privileges on that job. You can also set a job argument value if you have the CREATE ANY JOB privilege.

SET_JOB_ARGUMENT_VALUE can be used to set arguments of lightweight jobs but only if the argument is of type VARCHAR2.


SET_JOB_ATTRIBUTES Procedure

This procedure changes an attribute of a job.

Syntax

DBMS_SCHEDULER.SET_JOB_ATTRIBUTES (
   jobattr_array     IN JOBATTR_ARRAY,
   commit_semantics  IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-74 SET_JOB_ATTRIBUTES Procedure Parameters

Parameter Description

jobattr_array

The array of job attribute changes.

commit_semantics

The commit semantics. The following types are supported:

  • STOP_ON_FIRST_ERROR - this procedure returns on the first error and the previous attribute changes that were successful are committed to disk. This is the default.

  • TRANSACTIONAL - this procedure returns on the first error and everything that happened before that error is rolled back.

  • ABSORB_ERRORS - this procedure tries to absorb any errors and attempts the rest of the job attribute changes on the list and commits all the changes that were successful.


Usage Notes

Calling SET_ATTRIBUTE on an enabled job disables the job, changes the attribute value, and re-enables the job. SET_JOB_ATTRIBUTES changes the attribute values in the context of a single transaction.


SET_SCHEDULER_ATTRIBUTE Procedure

This procedure sets the value of a Scheduler attribute. This takes effect immediately but the resulting changes may not be seen immediately. The attributes you can set are default_timezone, max_job_slave_processes, and log_history.

Syntax

DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
   attribute      IN VARCHAR2,
   value          IN VARCHAR2);

Parameters

Table 114-75 SET_SCHEDULER_ATTRIBUTE Procedure Parameters

Parameter Description

attribute

The name of the Scheduler attribute. Possible values are:

  • default_timezone: It is very important that this attribute is set. Whenever a repeat_interval is specified without setting the start_date, the Scheduler needs to know which time zone it must apply to the repeat interval syntax. For example, if the repeat interval is specified as

    "FREQ=DAILY;BYHOUR=22"
    

    the job will repeat every day at 10pm, but 10pm in which time zone? If no start_date is specified the Scheduler will pick up the time zone from this default_timezone attribute. If you want your job or window to follow daylight savings adjustments, you must set this attribute to the proper region name. For instance, if your database resides in Paris, you would set this to 'Europe/Warsaw'.

    Daylight saving adjustments will not be followed if you specify an absolute offset. For example, '-8:00' would be correct for only half of the year in San Francisco. If no value is specified for this attribute, the Scheduler uses the time zone of systimestamp when the job or window is enabled. This is always an absolute offset and will not follow daylight savings adjustments.

  • log_history: This controls the number of days that log entries for both the job log and the window log are retained. It helps prevent logs from growing indiscriminately. The range of valid values is 0 through 999. If set to 0, no history is kept. Default value is 30. You can override this value at the job class level by setting a value for the log_history attribute of the job class.

  • max_job_slave_processes: This enables you to set a maximum number of slave processes for a particular system configuration and load. The default value is NULL, and the valid range is 1-999.

    Even though the Scheduler automatically determines what the optimum number of slave processes is for a given system configuration and load, you still might want to set a fixed limit on the Scheduler. If this is the case, you can set this attribute.

    Although the number set by max_job_slave_processes is a real maximum, it does not mean the Scheduler will start the specified number of slaves. For example, even though this attribute is set to 10, the Scheduler might still determine that is should not start more than 3 slave processes. However, if it wants to start 15, but it is set to 10, it will not start more than 10.

  • event_expiry_time: The time in seconds before an event generated by the Scheduler (in other words, a message enqueued by the Scheduler into the Scheduler event queue) expires (in other words, is automatically purged from the queue). If NULL, Scheduler-generated events expire after 24 hours.

value

The new value of the attribute


Usage Notes

To run SET_SCHEDULER_ATTRIBUTE, you must have the MANAGE SCHEDULER privilege.


STOP_JOB Procedure

This procedure stops currently running jobs or all jobs in a job class. Any instance of the job will be stopped. After stopping the job, the state of a one-time job will be set to STOPPED whereas the state of a repeating job will be set to SCHEDULED or COMPLETED depending on whether the next run of the job is scheduled.

If a job pointing to a chain is stopped, all steps of the running chain that are running are stopped.

For external jobs, STOP_JOB stops only the external process that was directly started by the job action. It does not stop child processes of external jobs.

Syntax

DBMS_SCHEDULER.STOP_JOB (
   job_name         IN VARCHAR2
   force            IN BOOLEAN DEFAULT FALSE
   commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');

Parameters

Table 114-76 STOP_JOB Procedure Parameters

Parameter Description

job_name

The name of the job or job class. Can be a comma-delimited list. For a job class, the SYS schema should be specified.

If the name of a job class is specified, the jobs that belong to that job class are stopped. The job class is not affected by this call.

force

If force is set to FALSE, the Scheduler tries to gracefully stop the job using an interrupt mechanism. This method gives control back to the slave process, which can update the status of the job in the job queue to stopped. If this fails, an error is returned.

If force is set to TRUE, the Scheduler will immediately terminate the job slave. Oracle recommends that STOP_JOB with force set to TRUE be used only after a STOP_JOB with force set to FALSE has failed.

Use of the force option requires the MANAGE SCHEDULER system privilege.

commit_semantics

The commit semantics. The following two types are supported:

  • STOP_ON_FIRST_ERROR - this procedure returns on the first error and the previous stop operations that were successful are committed to disk. This is the default.

  • ABSORB_ERRORS - this procedure tries to absorb any errors and attempts stopping the rest of the jobs and commits all the stop operations that were successful.


Usage Notes

STOP_JOB without the force option requires that you be the owner of the job or have ALTER privileges on that job. You can also stop a job if you have the CREATE ANY JOB or MANAGE SCHEDULER privilege.

STOP_JOB with the force option requires that you have the MANAGE SCHEDULER privilege.