3.14 DBMS_SCHEDULER

Table of Contents Previous Next


3 Built-In Packages : 3.14 DBMS_SCHEDULER

The DBMS_SCHEDULER package provides a way to create and manage Oracle-styled jobs, programs and job schedules. The DBMS_SCHEDULER package implements the following functions and procedures:
CREATE_JOB(job_name, job_type, job_action, number_of_arguments, start_date, repeat_interval, end_date, job_class, enabled, auto_drop, comments)
Use the first form of the CREATE_JOB procedure to create a job, specifying program and schedule details by means of parameters.
CREATE_JOB(job_name, program_name, schedule_name, job_class, enabled, auto_drop, comments)
Use the second form of CREATE_JOB to create a job that uses a named program and named schedule.
CREATE_PROGRAM(program_name, program_type, program_action, number_of_arguments, enabled, comments)
Use CREATE_PROGRAM to create a program.
CREATE_SCHEDULE(
schedule_name, start_date, repeat_interval, end_date, comments)
Use the CREATE_SCHEDULE procedure to create a schedule.
DEFINE_PROGRAM_ARGUMENT(
program_name, argument_position, argument_name, argument_type, default_value, out_argument)
Use the first form of the DEFINE_PROGRAM_ARGUMENT procedure to define a program argument that has a default value.
DEFINE_PROGRAM_ARGUMENT(
program_name, argument_position, argument_name, argument_type, out_argument)
Use the first form of the DEFINE_PROGRAM_ARGUMENT procedure to define a program argument that does not have a default value.
DISABLE(name, force, commit_semantics)
Use the DISABLE procedure to disable a job or program.
DROP_JOB(job_name, force, defer, commit_semantics)
Use the DROP_JOB procedure to drop a job.
DROP_PROGRAM(program_name, force)
Use the DROP_PROGRAM procedure to drop a program.
DROP_PROGRAM_ARGUMENT(
program_name, argument_position)
Use the first form of DROP_PROGRAM_ARGUMENT to drop a program argument by specifying the argument position.
DROP_PROGRAM_ARGUMENT(
program_name, argument_name)
Use the second form of DROP_PROGRAM_ARGUMENT to drop a program argument by specifying the argument name.
DROP_SCHEDULE(schedule_name, force)
Use the DROP SCHEDULE procedure to drop a schedule.
ENABLE(name, commit_semantics)
Use the ENABLE command to enable a program or job.
EVALUATE_CALENDAR_STRING(
calendar_string, start_date, return_date_after, next_run_date)
Use EVALUATE_CALENDAR_STRING to review the execution date described by a user-defined calendar schedule.
RUN_JOB(job_name, use_current_session, manually)
Use the RUN_JOB procedure to execute a job immediately.
SET_JOB_ARGUMENT_VALUE(
job_name, argument_position, argument_value)
Use the first form of SET_JOB_ARGUMENT value to set the value of a job argument described by the argument's position.
SET_JOB_ARGUMENT_VALUE(
job_name, argument_name, argument_value)
Use the second form of SET_JOB_ARGUMENT value to set the value of a job argument described by the argument's name.
Advanced Server's implementation of DBMS_SCHEDULER is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
The DBMS_SCHEDULER package is dependent on the pgAgent service; you must have a pgAgent service installed and running on your server before using DBMS_SCHEDULER.
Before using DBMS_SCHEDULER, a database superuser must create the catalog tables in which the DBMS_SCHEDULER programs, schedules and jobs are stored. Use the psql client to connect to the database, and invoke the command:
By default, the dbms_scheduler extension resides in the contrib/dbms_scheduler_ext subdirectory (under the Advanced Server installation).
Note that after creating the DBMS_SCHEDULER tables, only a superuser will be able to perform a dump or reload of the database.
The CREATE_JOB and CREATE_SCHEDULE procedures use Oracle-styled calendar syntax to define the interval with which a job or schedule is repeated. You should provide the scheduling information in the repeat_interval parameter of each procedure.
repeat_interval is a value (or series of values) that define the interval between the executions of the scheduled job. Each value is composed of a token, followed by an equal sign, followed by the unit (or units) on which the schedule will execute. Multiple token values must be separated by a semi-colon (;).
FREQ=predefined_interval
Where predefined_interval is one of the following: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY. The SECONDLY keyword is not supported.
BYMONTH=month(, month)...
Where month is the three-letter abbreviation of the month name: JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC
BYMONTH=month(, month)...
Where month is the numeric value representing the month: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12
BYMONTHDAY=day_of_month
Where day_of_month is a value from 1 through 31
BYDAY=weekday
Where weekday is a three-letter abbreviation or single-digit value representing the day of the week.
BYDATE=date(, date)...
Where date is YYYYMMDD.
YYYY is a four-digit year representation of the year,
MM is a two-digit representation of the month,
and DD is a two-digit day representation of the day.
BYDATE=date(, date)...
Where date is MMDD.
MM is a two-digit representation of the month,
and DD is a two-digit day representation of the day
Where hour is a value from 0 through 23.
BYMINUTE=minute
Where minute is a value from 0 through 59.
3.14.2 CREATE_JOB
Use the CREATE_JOB procedure to create a job. The procedure comes in two forms; the first form of the procedure specifies a schedule within the job definition, as well as a job action that will be invoked when the job executes:
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)
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_name
job_name specifies the optionally schema-qualified name of the job being created.
job_type specifies the type of job. The current implementation of CREATE_JOB supports a job type of PLSQL_BLOCK or STORED_PROCEDURE.
If job_type is PLSQL_BLOCK, job_action specifies the content of the PL/SQL block that will be invoked when the job executes. The block must be terminated with a semi-colon (;).
If job_type is STORED_PROCEDURE, job_action specifies the optionally schema-qualified name of the procedure.
number_of_arguments is an INTEGER value that specifies the number of arguments expected by the job. The default is 0.
start_date is a TIMESTAMP WITH TIME ZONE value that specifies the first time that the job is scheduled to execute. The default value is NULL, indicating that the job should be scheduled to execute when the job is enabled.
repeat_interval is a VARCHAR2 value that specifies how often the job will repeat. If a repeat_interval is not specified, the job will execute only once. The default value is NULL.
end_date is a TIMESTAMP WITH TIME ZONE value that specifies a time after which the job will no longer execute. If a date is specified, the end_date must be after start_date. The default value is NULL.
Please note that if an end_date is not specified and a repeat_interval is specified, the job will repeat indefinitely until it is disabled.
program_name is the name of a program that will be executed by the job.
schedule_name is the name of the schedule associated with the job.
job_class is accepted for compatibility and ignored.
enabled is a BOOLEAN value that specifies if the job is enabled when created. By default, a job is created in a disabled state, with enabled set to FALSE. To enable a job, specify a value of TRUE when creating the job, or enable the job with the DBMS_SCHEDULER.ENABLE procedure.
The auto_drop parameter is accepted for compatibility and is ignored. By default, a job's status will be changed to DISABLED after the time specified in end_date.
Use the comments parameter to specify a comment about the job.
The code fragment creates a job named update_log that executes each weeknight at 5:00. The job executes a PL/SQL block that inserts the current timestamp into a logfile (my_log). Since no end_date is specified, the job will execute until it is disabled by the DBMS_SCHEDULER.DISABLE procedure.
Use the CREATE_PROGRAM procedure to create a DBMS_SCHEDULER program. The signature is:
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)
program_name specifies the name of the program that is being created.
program_type specifies the type of program. The current implementation of CREATE_PROGRAM supports a program_type of PLSQL_BLOCK or PROCEDURE.
If program_type is PLSQL_BLOCK, program_action contains the PL/SQL block that will execute when the program is invoked. The PL/SQL block must be terminated with a semi-colon (;).
If program_type is PROCEDURE, program_action contains the name of the stored procedure.
If program_type is PLSQL_BLOCK, this argument is ignored.
If program_type is PROCEDURE, number_of_arguments specifies the number of arguments required by the procedure. The default value is 0.
enabled specifies if the program is created enabled or disabled:
If enabled is TRUE, the program is created enabled.
If enabled is FALSE, the program is created disabled; use the DBMS_SCHEDULER.ENABLE program to enable a disabled program.
Use the comments parameter to specify a comment about the program; by default, this parameter is NULL.
The following call to the CREATE_PROGRAM procedure creates a program named update_log:
update_log is a PL/SQL block that adds a row containing the current date and time to the my_log table. The program will be enabled when the CREATE_PROGRAM procedure executes.
Use the CREATE_SCHEDULE procedure to create a job schedule. The signature of the CREATE_SCHEDULE procedure is:
create_schedule(
schedule_name IN VARCHAR2,
start_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
repeat_interval IN VARCHAR2,
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
comments IN VARCHAR2 DEFAULT NULL)
schedule_name specifies the name of the schedule.
start_date is a TIMESTAMP WITH TIME ZONE value that specifies the date and time that the schedule is eligible to execute. If a start_date is not specified, the date that the job is enabled is used as the start_date. By default, start_date is NULL.
repeat_interval is a VARCHAR2 value that specifies how often the job will repeat. If a repeat_interval is not specified, the job will execute only once, on the date specified by start_date.
Please note: you must provide a value for either start_date or repeat_interval; if both start_date and repeat_interval are NULL, the server will return an error.
end_date IN TIMESTAMP WITH TIME ZONE DEFAULT NULL
end_date is a TIMESTAMP WITH TIME ZONE value that specifies a time after which the schedule will no longer execute. If a date is specified, the end_date must be after the start_date. The default value is NULL.
Please note that if a repeat_interval is specified and an end_date is not specified, the schedule will repeat indefinitely until it is disabled.
comments IN VARCHAR2 DEFAULT NULL)
Use the comments parameter to specify a comment about the schedule; by default, this parameter is NULL.
The following code fragment calls CREATE_SCHEDULE to create a schedule named weeknights_at_5:
The schedule executes each weeknight, at 5:00 pm, effective after June 1, 2013. Since no end_date is specified, the schedule will execute indefinitely until it is disabled with DBMS_SCHEDULER.DISABLE.
Use the DEFINE_PROGRAM_ARGUMENT procedure to define a program argument. The DEFINE_PROGRAM_ARGUMENT procedure comes in two forms; the first form defines an argument with a default value:
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)
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)
program_name is the name of the program to which the arguments belong.
argument_position specifies the position of the argument as it is passed to the program.
argument_name specifies the optional name of the argument. By default, argument_name is NULL.
argument_type IN VARCHAR2
argument_type specifies the data type of the argument.
default_value specifies the default value assigned to the argument. default_value will be overridden by a value specified by the job when the job executes.
out_argument IN BOOLEAN DEFAULT FALSE
out_argument is not currently used; if specified, the value must be FALSE.
The following code fragment uses the DEFINE_PROGRAM_ARGUMENT procedure to define the first and second arguments in a program named add_emp:
The first argument is an INTEGER value named dept_no that has a default value of 20. The second argument is a VARCHAR2 value named emp_name; the second argument does not have a default value.
3.14.6 DISABLE
Use the DISABLE procedure to disable a program or a job. The signature of the DISABLE procedure is:
disable(
name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR')
name specifies the name of the program or job that is being disabled.
force is accepted for compatibility, and ignored.
commit_semantics instructs the server how to handle an error encountered while disabling a program or job. By default, commit_semantics is set to STOP_ON_FIRST_ERROR, instructing the server to stop when it encounters an error. Any programs or jobs that were successfully disabled prior to the error will be committed to disk.
The TRANSACTIONAL and ABSORB_ERRORS keywords are accepted for compatibility, and ignored.
The following call to the DISABLE procedure disables a program named update_emp:
3.14.7 DROP_JOB
Use the DROP_JOB procedure to DROP a job, DROP any arguments that belong to the job, and eliminate any future job executions. The signature of the procedure is:
drop_job(
job_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE,
defer IN BOOLEAN DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR')
job_name specifies the name of the job that is being dropped.
force is accepted for compatibility, and ignored.
defer is accepted for compatibility, and ignored.
commit_semantics instructs the server how to handle an error encountered while dropping a program or job. By default, commit_semantics is set to STOP_ON_FIRST_ERROR, instructing the server to stop when it encounters an error.
The TRANSACTIONAL and ABSORB_ERRORS keywords are accepted for compatibility, and ignored.
The following call to DROP_JOB drops a job named update_log:
The DROP_PROGRAM procedure
The signature of the DROP_PROGRAM procedure is:
DROP_PROGRAM(
program_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE)
program_name specifies the name of the program that is being dropped.
force is a BOOLEAN value that instructs the server how to handle programs with dependent jobs.
Specify FALSE to instruct the server to return an error if the program is referenced by a job.
Specify TRUE to instruct the server to disable any jobs that reference the program before dropping the program.
The following call to DROP_PROGRAM drops a job named update_emp:
Use the DROP_PROGRAM_ARGUMENT procedure to drop a program argument. The DROP_PROGRAM_ARGUMENT procedure comes in two forms; the first form uses an argument position to specify which argument to drop:
drop_program_argument(
program_name IN VARCHAR2,
argument_position IN PLS_INTEGER)
drop_program_argument(
program_name IN VARCHAR2,
argument_name IN VARCHAR2)
program_name specifies the name of the program that is being modified.
argument_position specifies the position of the argument that is being dropped.
argument_name specifies the name of the argument that is being dropped.
The following call to DROP_PROGRAM_ARGUMENT drops the first argument in the update_emp program:
The following call to DROP_PROGRAM_ARGUMENT drops an argument named emp_name:
Use the DROP_SCHEDULE procedure to drop a schedule. The signature is:
DROP_SCHEDULE(
schedule_name IN VARCHAR2,
force IN BOOLEAN DEFAULT FALSE)
schedule_name specifies the name of the schedule that is being dropped.
force specifies the behavior of the server if the specified schedule is referenced by any job:
Specify FALSE to instruct the server to return an error if the specified schedule is referenced by a job. This is the default behavior.
Specify TRUE to instruct the server to disable to any jobs that use the specified schedule before dropping the schedule. Any running jobs will be allowed to complete before the schedule is dropped.
The following call to DROP_SCHEDULE drops a schedule named weeknights_at_5:
3.14.11 ENABLE
Use the ENABLE procedure to enable a disabled program or job.
The signature of the ENABLE procedure is:
ENABLE(
name IN VARCHAR2,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR')
name specifies the name of the program or job that is being enabled.
commit_semantics instructs the server how to handle an error encountered while enabling a program or job. By default, commit_semantics is set to STOP_ON_FIRST_ERROR, instructing the server to stop when it encounters an error.
The TRANSACTIONAL and ABSORB_ERRORS keywords are accepted for compatibility, and ignored.
The following call to DBMS_SCHEDULER.ENABLE enables the update_emp program:
Use the EVALUATE_CALENDAR_STRING procedure to evaluate the repeat_interval value specified when creating a schedule with the CREATE_SCHEDULE procedure. The EVALUATE_CALENDAR_STRING procedure will return the date and time that a specified schedule will execute without actually scheduling the job.
The signature of the EVALUATE_CALENDAR_STRING procedure is:
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)
calendar_string
calendar_string is the calendar string that describes a repeat_interval (see Section 3.14.1 that is being evaluated.
start_date IN TIMESTAMP WITH TIME ZONE
start_date is the date and time after which the repeat_interval will become valid.
Use the return_date_after parameter to specify the date and time that EVALUATE_CALENDAR_STRING should use as a starting date when evaluating the repeat_interval.
For example, if you specify a return_date_after value of 01-APR-13 09.00.00.000000, EVALUATE_CALENDAR_STRING will return the date and time of the first iteration of the schedule after April 1st, 2013.
next_run_date OUT TIMESTAMP WITH TIME ZONE
next_run_date is an OUT parameter that will contain the first occurrence of the schedule after the date specified by the return_date_after parameter.
3.14.13 RUN_JOB
Use the RUN_JOB procedure to execute a job immediately. The signature of the RUN_JOB procedure is:
run_job(
job_name IN VARCHAR2,
use_current_session IN BOOLEAN DEFAULT TRUE
job_name specifies the name of the job that will execute.
By default, the job will execute in the current session. If specified, use_current_session must be set to TRUE ; if use_current_session is set to FALSE, Advanced Server will return an error.
The following call to RUN_JOB executes a job named update_log:
Passing a value of TRUE as the second argument instructs the server to invoke the job in the current session.
Use the SET_JOB_ARGUMENT_VALUE procedure to specify a value for an argument. The SET_JOB_ARGUMENT_VALUE procedure comes in two forms; the first form specifies which argument should be modified by position:
set_job_argument_value(
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN VARCHAR2)
set_job_argument_value(
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN VARCHAR2)
Argument values set by the SET_JOB_ARGUMENT_VALUE procedure override any values set by default.
job_name specifies the name of the job to which the modified argument belongs.
Use argument_position to specify the argument position for which the value will be set.
Use argument_name to specify the argument by name for which the value will be set.
argument_value specifies the new value of the argument.
The following example assigns a value of 30 to the first argument in the update_emp job:
The following example sets the emp_name argument to SMITH:

3 Built-In Packages : 3.14 DBMS_SCHEDULER

Table of Contents Previous Next