SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SCHEDULER' UNION SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SCHEDULER';
Defined Data Types
TYPE bylist IS VARRAY (256) OF PLS_INTEGER;
Name
Constant
Data Type
Value
Yearly
Constant
PLS_INTEGER
1
Monthly
Constant
PLS_INTEGER
2
Weekly
Constant
PLS_INTEGER
3
Daily
Constant
PLS_INTEGER
4
Hourly
Constant
PLS_INTEGER
5
Minutely
Constant
PLS_INTEGER
6
Secondly
Constant
PLS_INTEGER
7
Monday
Constant
INTEGER
1
Tuesday
Constant
INTEGER
2
Wednesday
Constant
INTEGER
3
Thursday
Constant
INTEGER
4
Friday
Constant
INTEGER
5
Saturday
Constant
INTEGER
6
Sunday
Constant
INTEGER
7
TYPE SCHEDULER$_RULE_LIST IS TABLE OF sys.schedule; /
TYPE SCHEDULER$_STEP_TYPE_LIST IS TABLE OF sys.sch; /
TYPE SCHEDULER$_CHAIN_LINK_LIST IS TABLE OF sys.sc; /
TYPE SCHEDULER$_STEP_TYPE IS OBJECT ( step_name VARCHAR2(32), step_type VARCHAR2(32)); /
TYPE RE$VARIABLE_VALUE AS OBJECT ( variable_name VARCHAR2(32), variable_data sys.anydata)
-- For the definition of RE$NV_LIST: SELECT dbms_metadata.get_ddl('TYPE', 'RE$NV_LIST) FROM dual;
Execute Any Class
GRANT execute any class TO ;
GRANT execute any class TO uwclass;
Exernal Jobs
GRANT execute any program TO ;
GRANT execute any program TO uwclass;
System Privileges to Create External Jobs
GRANT create external job TO ;
GRANT create external job TO uwclass;
System Privileges to Create Internal Database Jobs
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.
dbms_scheduler.add_event_queue_subscriber( subscriber_name IN VARCHAR2 DEFAULT NULL);
SELECT owner, name FROM dba_queues ORDER BY 2,1;
set linesize 121 col retention format a20
SELECT queue_table, max_retries, retry_delay, retention FROM dba_queues WHERE name = 'SCHEDULER$_EVENT_QUEUE';
select consumer_name from dba_queue_subscribers where queue_name = 'SCHEDULER$_EVENT_QUEUE';
SELECT consumer_name FROM dba_queue_subscribers WHERE queue_name = 'SCHEDULER$_EVENT_QUEUE';
ADD_WINDOW_GROUP_MEMBER
Adds one or more windows to an existing window group
dbms_scheduler.add_window_group_member( group_name IN VARCHAR2, window_list IN VARCHAR2);
TBD
ALTER_CHAIN
Alters steps of a chain
dbms_scheduler.alter_chain( chain_name IN VARCHAR2, step_name IN VARCHAR2, attribute IN VARCHAR2, value IN BOOLEAN);
TBD
ALTER_RUNNING_CHAIN
Alters steps of a running chain
Overload 1
dbms_scheduler.alter_running_chain( job_name IN VARCHAR2, step_name IN VARCHAR2, attribute IN VARCHAR2, value IN BOOLEAN);
TBD
Overload 2
dbms_scheduler.alter_running_chain( job_name IN VARCHAR2, step_name IN VARCHAR2, attribute IN VARCHAR2, value IN VARCHAR2);
TBD
ANALYZE_CHAIN
Analyzes a chain or a list of steps and rules and outputs a list of chain dependencies
dbms_scheduler.analyze_chain( chain_name IN VARCHAR2, rules IN sys.scheduler$_rule_list, steps IN sys.scheduler$_step_type_list, step_pairs OUT sys.scheduler$_chain_link_list);
TBD
AUTO_PURGE
Purges from the logs based on class and global log_history
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.
dbms_scheduler.close_window(window_name IN VARCHAR2);
TBD
COPY_JOB
Copy a job. The new_job will contain all the attributes of the old_job, except that it will be created disabled
dbms_scheduler.copy_job(old_job IN VARCHAR2, new_job IN VARCHAR2);
dbms_scheduler.create_calendar_string( frequency IN PLS_INTEGER, interval IN PLS_INTEGER, bysecond IN bylist, byminute IN bylist, byhour IN bylist, byday_days IN bylist, byday_occurrence IN bylist, bymonthday IN bylist, byyearday IN bylist, byweekno IN bylist, bymonth IN bylist, calendar_string OUT VARCHAR2);
TBD
CREATE_CHAIN
Creates a chain. Chains are created disabled and must be enabled before use.
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);
TBD
CREATE_EVENT_SCHEDULE
Create a named event schedule
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);
TBD - Demo 2?
CREATE_JOB
Create a job in a single call (without using an existing program or schedule).
Overload 1
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);
See Scheduler Demos: Below
Overload 2
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, event_condition IN VARCHAR2, queue_spec IN VARCHAR2, 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);
See Scheduler Demos: Below
Create a job using an existing Program and Schedule
Overload 3
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);
See Scheduler Demos: Below
Create a job using an existing Program
Overload 4
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);
See Scheduler Demos: Below
Overload 5
dbms_scheduler.create_job( job_name IN VARCHAR2, program_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, job_class IN VARCHAR2 DEFAULT 'DEFAULT_JOB_CLASS', enabled IN BOOLEAN DEFAULT FALSE, auto_drop IN BOOLEAN DEFAULT TRUE, comments IN VARCHAR2 DEFAULT NULL);
See Scheduler Demos: Below
Overload 6
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);
BEGIN dbms_resource_manager.create_consumer_group('Workers', 'Those that do actual work'); END; /
BEGIN dbms_scheduler.create_job_class( job_class_name => 'finance_jobs', resource_consumer_group => 'Workers'); END; /
CREATE_PROGRAM
Create a new program
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);
See Scheduler Demos: Below
CREATE_SCHEDULE
Creates a schedule
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);
BEGIN dbms_scheduler.create_schedule('embed_sched', repeat_interval => 'FREQ=YEARLY;BYDATE=0130,0220,0725');
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; /
See Scheduler Demos: Below
CREATE_WINDOW
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.
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);
dbms_scheduler.create_window_group( group_name IN VARCHAR2, window_list IN VARCHAR2 DEFAULT NULL, comments IN VARCHAR2 DEFAULT NULL);
BEGIN dbms_scheduler.create_window_group( group_name => 'downtime', window_list => 'weeknights, weekends' comments => 'Group of system maintenance windows'); END; /
DEFINE_ANYDATA_ARGUMENT
Define an argument with a default value encapsulated in an ANYDATA data type
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);
TBD
DEFINE_CHAIN_EVENT_STEP
Adds or replaces a chain step and associates it with an inline schedule
Overload 1
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);
TBD
Adds or replaces a chain step and associates it with an inline event
Overload 2
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);
TBD
DEFINE_CHAIN_RULE
Adds or replaces a chain rule
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);
TBD
DEFINE_CHAIN_STEP
Adds or replaces a chain step and associates it with a program or chain
dbms_scheduler.define_chain_step( chain_name IN VARCHAR2, step_name IN VARCHAR2, program_name IN VARCHAR2);
TBD
DEFINE_METADATA_ARGUMENT
Define a special metadata argument for the program
dbms_scheduler.define_metadata_argument( program_name IN VARCHAR2, metadata_attribute IN VARCHAR2, argument_position IN PLS_INTEGER, argument_name IN VARCHAR2 DEFAULT NULL);
TBD
DEFINE_PROGRAM_ARGUMENT
Define an argument of a program
Overload 1
dbms_scheduler.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);
See Scheduler Demos: Below
Overload 2
dbms_scheduler.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);
See Scheduler Demos: Below
DISABLE
Disable a program, chain, job, window or window_group. The procedure will NOT return an error if the object was already disabled.
dbms_scheduler.disable( name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
See Scheduler Demos: Below
DISABLE1_CALENDAR_CHECK
Undocumented
dbms_scheduler.disable1_calendar_check;
dbms_scheduler.disable1_calendar_check;
DROP_CHAIN
Drop a chain
dbms_scheduler.drop_chain( chain_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD
DROP_CHAIN_RULE
Drop a chain rule
dbms_scheduler.drop_chain_rule( chain_name IN VARCHAR2, rule_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD
DROP_CHAIN_STEP
Drop a chain step
dbms_scheduler.drop_chain_step( chain_name IN VARCHAR2, step_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD
DROP_JOB
Drop a job or several jobs
dbms_scheduler.drop_job( job_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
See Scheduler Demos: Below
DROP_JOB_CLASS
Drop a job class
dbms_scheduler.drop_job_class( job_class_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
Drops an existing program (or a comma separated list of programs)
dbms_scheduler.drop_program( program_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
See Scheduler Demo: Below
DROP_PROGRAM_ARGUMENT
Drop a program argument either by name or position
Overload 1
dbms_scheduler.drop_program_argument( program_name IN VARCHAR2, argument_position IN PLS_INTEGER);
See Scheduler Demos: Below
Overload 2
dbms_scheduler.drop_program_argument( program_name IN VARCHAR2, argument_name IN VARCHAR2);
See Scheduler Demos: Below
DROP_SCHEDULE
Drop a schedule (or comma-separated list of schedules)
dbms_scheduler.drop_schedule( schedule_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD - Demo2 ?
DROP_WINDOW
Drops a window. All metadata about the window is removed from the database. All references to the window are removed from window groups.
dbms_scheduler.drop_window( window_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD
DROP_WINDOW_GROUP
Drops a window group but not the windows that are members of this window group.
dbms_scheduler.drop_window_group( group_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
TBD
ENABLE
Enable a program, chain, job, window or window group. The procedure will NOT return an error if the object was already enabled.
dbms_scheduler.enable(name IN VARCHAR2);
See Scheduler Demos: Below
EVALUTE_CALENDAR_STRING
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
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);
set serveroutput on;
alter session set NLS_DATE_FORMAT = '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_scheduler.generate_job_name( prefix IN VARCHAR2 DEFAULT 'JOB$_') RETURN VARCHAR2;
SELECT dbms_scheduler.generate_job_name FROM dual;
SELECT dbms_scheduler.generate_job_name('UW') FROM dual;
GET_ATTRIBUTE
Retrieve an attribute
Overload 1
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT PLS_INTEGER);
TBD
Overload 2
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT BOOLEAN);
TBD
Overload 3
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT DATE);
TBD
Overload 4
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT TIMESTAMP);
TBD
Overload 5
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT TIMESTAMP WITH TIME ZONE);
TBD
Overload 6
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT TIMESTAMP WITH LOCAL TIME ZONE);
TBD
Overload 7
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT INTERVAL DAY TO SECOND);
TBD
Overload 8
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT VARCHAR2);
TBD
Overload 9
dbms_scheduler.get_attribute( name IN VARCHAR2, attribute IN VARCHAR2, value OUT VARCHAR2, value2 OUT VARCHAR2);
TBD
GET_CHAIN_RULE_ACTION
Used by chain views to output rule actions
dbms_scheduler.get_chain_rule_action(action_in IN re$nv_list) RETURN VARCHAR2;
TBD
GET_CHAIN_RULE_CONDITION
Used by chain views to output rule conditions
dbms_scheduler.get_chain_rule_condition( action_in IN re$nv_list, condition_in IN VARCHAR2) RETURN VARCHAR2;
TBD
GET_DEFAULT_VALUE
This accepts an attribute name and returns the default value. If the attribute is not recognized it returns NULL. If the attribute is of type BOOLEAN, it will return 'TRUE' or 'FALSE'.
INSERT INTO t VALUES (sys.anyData.convertVarchar2('hello world'));
SELECT * FROM t;
SELECT dbms_scheduler.get_varchar2_value(mycol) FROM t;
OPEN_WINDOW
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.
dbms_scheduler_open_window( window_name IN VARCHAR2, duration IN INTERVAL DAY TO SECOND, force IN BOOLEAN DEFAULT FALSE);
TBD
PURGE_LOG
Purges from the logs based on the arguments. The default is to purge all entries
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);
TBD
REMOVE_EVENT_QUEUE_SUBSCRIBER
Remove subscriber from the SCHEDULER queue
dbms_scheduler.remove_event_queue_subscriber( subscriber_name IN VARCHAR2 DEFAULT NULL);
See ADD_EVENT_QUEUE_SUBSCRIBER Demo: Above
REMOVE_WINDOW_GROUP_MEMBER
Removes one or more windows from an existing window group
dbms_scheduler.remove_window_group_member( group_name IN VARCHAR2, window_list IN VARCHAR2);
TBD
RESET_JOB_ARGUMENT_VALUE
Clear a previously set job argument value
Overload 1
dbms_scheduler.reset_job_argument_value( job_name IN VARCHAR2, argument_position IN PLS_INTEGER);
dbms_scheduler.resolve_calendar_string( calendar_string IN VARCHAR2, frequency OUT PLS_INTEGER, interval OUT PLS_INTEGER, calendars_used OUT BOOLEAN, bysecond OUT scheduler$_int_array_type, byminute OUT scheduler$_int_array_type, byhour OUT scheduler$_int_array_type, byday_days OUT scheduler$_int_array_type, byday_occurrence OUT scheduler$_int_array_type, bydate_y OUT scheduler$_int_array_type, bydate_md OUT scheduler$_int_array_type, bymonthday OUT scheduler$_int_array_type, byyearday OUT scheduler$_int_array_type, byweekno OUT scheduler$_int_array_type, bymonth OUT scheduler$_int_array_type, bysetpos OUT scheduler$_int_array_type);
TBD
Overload 2
dbms_scheduler.resolve_calendar_string( calendar_string IN VARCHAR2, frequency OUT PLS_INTEGER, interval OUT PLS_INTEGER, bysecond OUT BYLIST, byminute OUT BYLIST, byhour OUT BYLIST, byday_days OUT BYLIST, byday_occurrence OUT BYLIST,
