Oracle DBMS_SCHEDULER
Version 11.2.0.3
 
General Information
Source {ORACLE_HOME}/rdbms/admin/dbmssch.sql
First Available 10.1
Constants
Name Data Type Value
General
logging_off PLS_INTEGER 32
logging_runs PLS_INTEGER 64
logging_failed_runs PLS_INTEGER 128
logging_full PLS_INTEGER 256
 Defaults for Job E-mail Notification
default_notification_subject VARCHAR2(100) 'Oracle Scheduler Job Notification - %job_owner%.%job_name%.%job_subname% %event_type%';
default_notification_body VARCHAR2(300) 'Job: %job_owner%.%job_name%.%job_subname% Event: %event_type% Date: %event_timestamp%
Log id: %log_id% Job class: %job_class_name% Run count: %run_count% Failure count: %failure_count%
Retry count: %retry_count% Error code: %error_code% Error message: %error_message%';
 Raise Flag Events
job_started PLS_INTEGER 1
job_succeeded PLS_INTEGER 2
job_failed PLS_INTEGER 4
job_broken PLS_INTEGER 8
job_completed PLS_INTEGER 16
job_stopped PLS_INTEGER 32
job_sch_lim_reached PLS_INTEGER 64
job_disabled PLS_INTEGER 128
job_chain_stalled PLS_INTEGER 256
job_all_events PLS_INTEGER 511
job_over_max_dur PLS_INTEGER 512
job_run_completed PLS_INTEGER job_succeeded+job_failed+job_stopped;
Commit Semantics
ABSORB_ERRORS 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.
STOP_ON_FIRST_ERROR Procedure returns on the first error and the previous attribute changes that were successful are committed to disk. This is the default
TRANSACTIONAL Procedure returns on the first error and everything that happened before that error is rolled back
Dependencies SELECT name FROM dba_dependencies WHERE referenced_name = 'DBMS_SCHEDULER'
UNION
SELECT referenced_name FROM dba_dependencies WHERE name = 'DBMS_SCHEDULER'
ORDER BY 1;
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;

-- file watcher
TYPE scheduler_filewatcher_result IS OBJECT (
destination       VARCHAR2(4000),
directory_path    VARCHAR2(4000),
actual_file_name  VARCHAR2(4000),
file_size         NUMBER,
file_timestamp    TIMESTAMP WITH TIME ZONE,
ts_ms_from_epoch  NUMBER,
matching_requests SYS.SCHEDULER_FILEWATCHER_REQ_LIST);

TYPE scheduler_filewatcher_request IS OBJECT (
owner               VARCHAR2(4000),
name                VARCHAR2(4000),
requested_path_name VARCHAR2(4000),
requested_file_name VARCHAR2(4000),
credential_owner    VARCHAR2(4000),
credential_name     VARCHAR2(4000),
min_file_size       NUMBER,
steady_state_dur    NUMBER);
Security Model Execute is granted to PUBLIC under AUTHID CURRENT_USER
Security Privileges
Privilege Description
Create Any Jobs This privilege enables you to create, alter, and drop jobs, chains, schedules, and programs in any schema except SYS. This privilege is very powerful and should be used with care because it allows the grantee to execute code as any other user.
Create External Jobs Required to create jobs that run outside of the database. Owners of jobs of type 'EXECUTABLE' or jobs that point to programs of type 'EXECUTABLE' require this privilege. To run a job of type 'EXECUTABLE', you must have this privilege and the CREATE JOB privilege.
Create Job This privilege enables you to create jobs, chains, schedules, and programs in your own schema. You will always be able to alter and drop jobs, schedules and programs in your own schema, even if you do not have the CREATE JOB privilege. In this case, the job must have been created in your schema by another user with the CREATE ANY JOB privilege.
Execute Any Class Enables jobs to run under any job class
Execute Any Program Enables jobs to use programs or chains from any schema
Manage Scheduler This is the most important privilege for administering the Scheduler. It enables you to create, alter, and drop job classes, windows, and window groups. It also enables you to set and retrieve Scheduler attributes and purge Scheduler logs.
System Privileges to Create Chains dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_rule_obj, '<schema_name>')

dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_rule_set_obj, '<schema_name>')

dbms_rule_adm.grant_system_privilege(
dbms_rule_adm.create_evaluation_context_obj, '<schema_name>')
BEGIN
  dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_obj, 'uwclass');
  dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_rule_set_obj, 'uwclass');
  dbms_rule_adm.grant_system_privilege(dbms_rule_adm.create_evaluation_context_obj, 'uwclass');
END;
/
Subprograms
ADD_EVENT_QUEUE_SUBSCRIBER DEFINE_PROGRAM_ARGUMENT GET_FILE
ADD_GROUP_MEMBER DISABLE GET_JOB_STEP_CF
ADD_JOB_EMAIL_NOTIFICATION DISABLE1_CALENDAR_CHECK GET_SCHEDULER_ATTRIBUTE
ADD_WINDOW_GROUP_MEMBER DROP_AGENT_DESTINATION GET_SYS_TIME_ZONE_NAME
ALTER_CHAIN DROP_CHAIN GET_VARCHAR2_VALUE
ALTER_RUNNING_CHAIN DROP_CHAIN_RULE IS_SCHEDULER_CREATED_AGENT
ANALYZE_CHAIN DROP_CHAIN_STEP OPEN_WINDOW
AUTO_PURGE DROP_CREDENTIAL PURGE_LOG
CHECK_SYS_PRIVS DROP_DATABASE_DESTINATION PUT_FILE
CLOSE_WINDOW DROP_FILE_WATCHER REMOVE_EVENT_QUEUE_SUBSCRIBER
COPY_JOB DROP_GROUP REMOVE_GROUP_MEMBER
CREATE_CALENDAR_STRING DROP_JOB REMOVE_JOB_EMAIL_NOTIFICATION
CREATE_CHAIN DROP_JOB_CLASS REMOVE_WINDOW_GROUP_MEMBER
CREATE_CREDENTIAL DROP_PROGRAM RESET_JOB_ARGUMENT_VALUE
CREATE_DATABASE_DESTINATION DROP_PROGRAM_ARGUMENT RESOLVE_CALENDAR_STRING
CREATE_EVENT_SCHEDULE DROP_SCHEDULE RESOLVE_NAME
CREATE_FILE_WATCHER DROP_WINDOW RUN_CHAIN
CREATE_GROUP DROP_WINDOW_GROUP RUN_JOB
CREATE_JOB ENABLE SET_AGENT_REGISTRATION_PASS
CREATE_JOBS END_DETACHED_JOB_RUN SET_ATTRIBUTE
CREATE_JOB_CLASS EVALUATE_CALENDAR_STRING SET_ATTRIBUTE_NULL
CREATE_PROGRAM EVALUATE_RUNNING_CHAIN SET_JOB_ANYDATA_VALUE
CREATE_SCHEDULE FILE_WATCH_FILTER SET_JOB_ARGUMENT_VALUE
CREATE_WINDOW GENERATE_EVENT_LIST SET_JOB_ATTRIBUTES
CREATE_WINDOW_GROUP GENERATE_JOB_NAME SET_SCHEDULER_ATTRIBUTE
DEFINE_ANYDATA_ARGUMENT GET_AGENT_VERSION SHOW_ERRORS
DEFINE_CHAIN_EVENT_STEP GET_ATTRIBUTE STIME
DEFINE_CHAIN_RULE GET_CHAIN_RULE_ACTION STOP_JOB
DEFINE_CHAIN_STEP GET_CHAIN_RULE_CONDITION SUBMIT_REMOTE_EXTERNAL_JOB
DEFINE_METADATA_ARGUMENT GET_DEFAULT_VALUE  
 
ADD_EVENT_QUEUE_SUBSCRIBER
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';

exec dbms_scheduler.add_event_queue_subscriber('UWCLASS');

SELECT consumer_name
FROM dba_queue_subscribers
WHERE queue_name = 'SCHEDULER$_EVENT_QUEUE';

exec dbms_scheduler.remove_event_queue_subscriber('UWCLASS');

SELECT consumer_name
FROM dba_queue_subscribers
WHERE queue_name = 'SCHEDULER$_EVENT_QUEUE';
 
ADD_GROUP_MEMBER
??? dbms_scheduler.add_group_member(group_name IN VARCHAR2, member IN VARCHAR2);
xxx
 
ADD_JOB_EMAIL_NOTIFICATION
??? dbms_scheduler.add_job_email_notification(
job_name         IN VARCHAR2,
recipients       IN VARCHAR2,
sender           IN VARCHAR2 DEFAULT NULL,
subject          IN VARCHAR2 DEFAULT dbms_scheduler.default_notification_subject,
body             IN VARCHAR2 DEFAULT dbms_scheduler.default_notification_body,
events           IN VARCHAR2 DEFAULT 'JOB_FAILED,JOB_BROKEN,JOB_SCH_LIM_REACHED,
                                      JOB_CHAIN_STALLED,JOB_OVER_MAX_DUR',
filter_condition IN VARCHAR2 DEFAULT NULL);
xxx
 
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);
See CREATE_WINDOW_GROUP Demo Below
 
ALTER_CHAIN
Alters steps of a chain

Overload 1		 dbms_scheduler.alter_chain(
chain_name IN VARCHAR2,
step_name  IN VARCHAR2,
attribute  IN VARCHAR2,
value      IN BOOLEAN);
See CREATE_CHAIN Demo Below
Overload 2 dbms_scheduler.alter_chain(
chain_name IN VARCHAR2,
step_name  IN VARCHAR2,
attribute  IN VARCHAR2,
char_value IN VARCHAR2);
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);
See CREATE_CHAIN Demo Below
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 dbms_scheduler.auto_purge;
exec dbms_scheduler.auto_purge;
 
CHECK_SYS_PRIVS
Internal / Undocumented dbms_scheduler.check_sys_privs RETURN PLS_INTEGER;
conn uwclass/uwclass

SELECT dbms_scheduler.check_sys_privs
FROM dual;

conn / as sysdba

SELECT dbms_scheduler.check_sys_privs
FROM dual;
 
CLOSE_WINDOW
Closes an open window prematurely. A closed window means that it is no longer in effect. dbms_scheduler.close_window(window_name IN VARCHAR2);
exec dbms_scheduler.close_window('weeknights');
 
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);
SELECT owner, job_name, enabled
FROM dba_scheduler_jobs

exec dbms_scheduler.copy_job('PURGE_LOG', 'TEST');

SELECT owner, job_name, enabled
FROM dba_scheduler_jobs;

exec dbms_scheduler.drop_job('TEST', TRUE);

SELECT owner, job_name, enabled
FROM dba_scheduler_jobs;
 
CREATE_CALENDAR_STRING
Undocumented 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);
desc dba_scheduler_chains

SELECT owner, chain_name, rule_set_owner, rule_set_name, number_of_rules
FROM dba_scheduler_chains;

exec dbms_scheduler.create_chain('TEST_CHAIN');

SELECT owner, chain_name, rule_set_owner, rule_set_name, number_of_rules
FROM dba_scheduler_chains;

desc dba_scheduler_chain_steps

SELECT chain_name, step_name, program_name, step_type
FROM dba_scheduler_chain_steps;

BEGIN
  dbms_scheduler.define_chain_step('TEST_CHAIN', 'STEP1', 'PROGRAM1');
  dbms_scheduler.define_chain_step('TEST_CHAIN', 'STEP2', 'PROGRAM2');
END;
/

SELECT chain_name, step_name, program_name, event_schedule_name
FROM dba_scheduler_chain_steps;

BEGIN
  dbms_scheduler.define_chain_event_step('TEST_CHAIN','STEP2','SCHED1');
END;
/

SELECT chain_name, step_name, program_name, event_schedule_name
FROM dba_scheduler_chain_steps;

desc dba_scheduler_chain_rules

SELECT chain_name, rule_name, condition, action
FROM dba_scheduler_chain_rules;

BEGIN
  dbms_scheduler.define_chain_rule('TEST_CHAIN','TRUE', 'START step1', 'step1_rule', 'begin chain run');
  dbms_scheduler.define_chain_rule('TEST_CHAIN', 'step1 completed', START step2', 'step2_rule');
END;
/

SELECT chain_name, rule_name, condition, action
FROM dba_scheduler_chain_rules;

exec dbms_scheduler.enable('TEST_CHAIN');

desc dba_scheduler_jobs

col job_name format a30
col job_type format a16
col job_action format a70
col repeat_interval format a28

SELECT job_name, job_type, job_action
FROM dba_scheduler_jobs;

BEGIN
  dbms_scheduler.create_job('JOB1', job_type => 'CHAIN', job_action => 'TEST_CHAIN',
  repeat_interval => 'freq=daily;byhour=22;byminute=30;bysecond=0',
  enabled => TRUE);
END;
/

SELECT job_name, job_type, job_action
FROM dba_scheduler_jobs;

BEGIN
  dbms_scheduler.alter_chain ('TEST_CHAIN', 'STEP1', attribute => 'SKIP', value => TRUE);
END;
/

BEGIN
  dbms_scheduler.run_chain('TEST_CHAIN', 'JOB1', start_steps => 'JOB_STEP1, JOB_STEP2');
END;
/

BEGIN
  dbms_scheduler.alter_running_chain ('TEST_CHAIN', 'JOB1', 'STEP2', attribute => 'PAUSE', value => TRUE);
END;
/

exec dbms_scheduler.drop_chain_rule('TEST_CHAIN', 'STEP1_RULE', TRUE);

exec dbms_scheduler.drop_chain_step('TEST_CHAIN', 'STEP2', TRUE);

exec dbms_scheduler.disable('TEST_CHAIN');

exec dbms_scheduler.drop_chain('TEST_CHAIN');
 
CREATE_CREDENTIAL
Create a new credential dbms_scheduler.create_credential(
credential_name IN VARCHAR2,
username        IN VARCHAR2, -- operating system user
password        IN VARCHAR2, -- and corresponding pwd
database_role   IN VARCHAR2 DEFAULT NULL,
windows_domain  IN VARCHAR2 DEFAULT NULL,
comments        IN VARCHAR2 DEFAULT NULL);
desc dba_scheduler_credentials

col owner format a5
col username format a20

SELECT owner, credential_name, username
FROM dba_scheduler_credentials;

BEGIN
  dbms_scheduler.create_credential('uw_credential', 'uwclass', 'oracle1');
END;
/

SELECT owner, credential_name, username
FROM dba_scheduler_credentials;

SELECT object_name, object_type
FROM user_objects;

GRANT EXECUTE ON uw_credential TO uwclass;

SELECT grantee, owner, table_name, privilege
FROM dba_tab_privs
WHERE grantee = 'UWCLASS';

exec dbms_scheduler.drop_credential('uw_credential', TRUE);

SELECT owner, credential_name, username
FROM dba_scheduler_credentials;

SELECT object_name, object_type
FROM user_objects;
 
CREATE_DATABASE_DESTINATION
Creates as remote database destination that represents a remote database. The agent value must be an existing external destination name. dbms_scheduler.create_database_destination(
destination_name IN VARCHAR2,
agent            IN VARCHAR2,
tns_name         IN VARCHAR2,
comments         IN VARCHAR2 DEFAULT NULL);
See DROP_DATABASE_DESTINATION Demo Below
 
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);
desc dba_scheduler_schedules

col schedule_name format a20
col event_condition format a15

SELECT schedule_name, start_date, event_queue_name, event_condition
FROM dba_scheduler_schedules;

-- to run the following an appropriate queue must be
-- created the AQ code to do this is not included here
BEGIN
  dbms_scheduler.create_event_schedule('TEST_EVENTS_SCHED', SYSTIMESTAMP,
  event_condition => 'tab.user_data.event_type = ''ZERO_BALANCE''',
  queue_spec => 'entry_events_q, entry_agent1');
END;
/

SELECT schedule_name, start_date, event_queue_name, event_condition
FROM dba_scheduler_schedules;

exec dbms_scheduler.drop_schedule('TEST_EVENTS_SCHED', TRUE);
BEGIN
  dbms_scheduler.create_event_schedule('uwclass.file_arrival', SYSTIMESTAMP,
  'tab.user_data.object_owner = ''UWCLASS'' AND
   tab.user_data.event_name = ''FILE_ARRIVAL'' AND
   extract hour from tab.user_data.event_timestamp < 9',
  'entry_events_q');

  dbms_scheduler.create_job(
    job_name        => 'UW_FILE_LOAD',
    program_name    => 'Run_LOAD_DATA',
    start_date      => '01-JUL-2009 2.30.00AM US/Pacific',
    event_condition => 'tab.user_data.event_name = ''FILE_ARRIVAL''',
    queue_spec      => 'entry_events_q'
    enabled         => TRUE,
    auto_drop       => FALSE,
    comments => 'UW Demo Job');
END;
/
 
CREATE_FILE_WATCHER (new 11.2.0.1)
Creates a file watcher, which is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job dbms_scheduler.create_file_watcher(
file_watcher_name     IN VARCHAR2,
directory_path        IN VARCHAR2,
file_name             IN VARCHAR2,
credential_name       IN VARCHAR2,
destination           IN VARCHAR2    DEFAULT NULL,
min_file_size         IN PLS_INTEGER DEFAULT 0,
steady_state_duration IN INTERVAL DAY TO SECOND DEFAULT NULL,
comments              IN VARCHAR2    DEFAULT NULL,
enabled               IN BOOLEAN     DEFAULT TRUE);
See File Watcher Demo Below
 
CREATE_GROUP
Creates a destination group to be set as a job destination dbms_scheduler.create_group(
group_name IN VARCHAR2,
group_type IN VARCHAR2,
member     IN VARCHAR2 DEFAULT NULL,
comments   IN VARCHAR2 DEFAULT NULL);
xxx
 
CREATE_JOB (new 11.2.0.1 parameters)
Create a job in a single call (without using an existing program or schedule).

This demo create a job that runs the load_vip_table stored procedure every 3rd Saturday of the month at 11:15.

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,
credential_name     IN VARCHAR2    DEFAULT NULL,
destination_name    IN VARCHAR2    DEFAULT NULL);
BEGIN
  sys.dbms_scheduler.create_job (
      job_name => 'UWCLASS.VIP_TABLE_LOAD',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN uwclass.load_vip_table; END; ',
      start_date => TRUNC(SYSDATE+4)+(11.25/24),
      repeat_interval => 'FREQ=MONTHLY; BYDAY=3SAT',
      end_date => NULL,
      job_class => 'DEFAULT_JOB_CLASS',
      comments => 'Populate the VIP table with names and site codes');

  sys.dbms_scheduler.enable('uwclass.vip_table_load');
END;
/
Create a job using an inlined event schedule

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                 DEFAULT NULL,
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,
credential_name     IN VARCHAR2                 DEFAULT NULL,
destination_name    IN VARCHAR2                 DEFAULT NULL);
TBD
Create a job using an existing, named, schedule object and a named program object

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
job_style        IN VARCHAR2 DEFAULT 'REGULAR',
credential_name  IN VARCHAR2 DEFAULT NULL,
destination_name IN VARCHAR2 DEFAULT NULL);
BEGIN
  sys.dbms_scheduler.create_job(
      job_name => 'UWCLASS.JIT_INVENTORY',
      program_name => 'jit_replenish',
      schedule_name => 'jit_reorder_schedule',
      comments => 'Just-In-Time Reorder Job');

  sys.dbms_scheduler.enable('uwclass.vip_table_load');
END;
/
Create a job using an existing, named, program object and an inlined schedule

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,
job_style        IN VARCHAR2                 DEFAULT 'REGULAR',
credential_name  IN VARCHAR2                 DEFAULT NULL,
destination_name IN VARCHAR2                 DEFAULT NULL);
See Scheduler Demo1 Below
Create a job using a named program and inlined event schedule

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,
job_style        IN VARCHAR2                 DEFAULT 'REGULAR',
credential_name  IN VARCHAR2                 DEFAULT NULL,
destination_name IN VARCHAR2                 DEFAULT NULL);
BEGIN
  sys.dbms_scheduler.create_job(
      job_name => 'UWCLASS.JIT_INVENTORY',
      program_name => 'jit_replenish',
      event_condition => 'tab.user_data.event_type = ''REORDER_POINT''',
      queue_spec => 'jit_q, jit_agent',
      comments => 'Just-In-Time Reorder Job');

  sys.dbms_scheduler.enable('uwclass.jit_inventory');
/
Create a job using a named schedule object and an inlined program

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,
credential_name     IN VARCHAR2    DEFAULT NULL,
destination_name    IN VARCHAR2    DEFAULT NULL);
BEGIN
  dbms_scheduler.create_job(
  job_name            => 'PURGE_JOB',
  schedule_name       => 'PURGE_SCHED', -- see CREATE_SCHEDULE demo
  job_type            => 'PLSQL_BLOCK',
  job_action          => 'BEGIN purge_proc END; ',
  number_of_arguments => 0,
  job_class           => 'DEFAULT_JOB_CLASS',
  enabled             => FALSE
  auto_drop           => FALSE,
  comments            => 'Demonstration job using a schedule object');

  dbms_scheduler.set_attribute('PURGE_JOB', 'job_priority', 2);
  dbms_scheduler.enable('PURGE_JOB');
END;
/
 
CREATE_JOBS
Batch create job

Overload 1		 dbms_scheduler.create_jobs(
jobdef_array     IN sys.job_definition_array,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
TBD
Overload 2

Deprecated		 dbms_scheduler.create_jobs(
job_array        IN sys.job_array,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
TBD
 
CREATE_JOB_CLASS
Create a job class dbms_scheduler.create_job_class(
job_class_name          VARCHAR2,
resource_consumer_group VARCHAR2    DEFAULT NULL,
service                 VARCHAR2    DEFAULT NULL,
logging_level           PLS_INTEGER DEFAULT DBMS_SCHEDULER.LOGGING_RUNS,
log_history             PLS_INTEGER DEFAULT NULL,
comments                VARCHAR2    DEFAULT NULL);
desc dba_scheduler_job_classes

col logging_level format a15

SELECT job_class_name, resource_consumer_group, logging_level
FROM dba_scheduler_job_classes;

BEGIN
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.create_consumer_group('Workers', 'Those that do actual work');
  dbms_resource_manager.submit_pending_area;

  dbms_scheduler.create_job_class('finance_jobs', 'Workers');
END;
/

SELECT job_class_name, resource_consumer_group, logging_level
FROM dba_scheduler_job_classes;

exec dbms_scheduler.drop_job_class('finance_jobs', TRUE);

BEGIN
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.delete_consumer_group('Workers');
  dbms_resource_manager.submit_pending_area;
END;
/
 
CREATE_PROGRAM
Create a new program based on a PL/SQL Block 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);
BEGIN
  sys.dbms_scheduler.create_program(
      program_name => 'ProcessBackorders1',
      program_type => 'PLSQL_BLOCK',
      program_action => 'BEGIN uwclass.process_backorders; END;',
      comments => 'Create POs from backorders');

  sys.dbms_scheduler.enable(name => 'ProcessBackorders1');
END;
/

col program_name format a20
col program_action format a55

SELECT program_name, program_type, program_action, enabled
FROM dba_scheduler_programs
WHERE owner = 'UWCLASS';
Create a new program based on a Shell script BEGIN
  sys.dbms_scheduler.create_program(
      program_name => 'ProcessBackorders2',
      program_type => 'EXECUTABLE',
      program_action => '/app/oracle/orabase/scripts/process_backorders.sh',
      comments => 'Create POs from backorders');

  sys.dbms_scheduler.enable(name => 'ProcessBackorders2');
END;
/

SELECT program_name, program_type, program_action, enabled
FROM dba_scheduler_programs
WHERE owner = 'UWCLASS';
Create a new program based on a stored procedure BEGIN
  sys.dbms_scheduler.create_program(
      program_name => 'ProcessBackorders3',
      program_type => 'STORED_PROCEDURE',
      program_action => 'uwclass.process_backorders',
      number_of_arguments => 1,
      comments => 'Create POs from backorders');

  sys.dbms_scheduler.define_program_argument(
      program_name => 'ProcessBackorders3',
      argument_name => 'DaysToProcess',
      argument_position => 1,
      argument_type => 'NUMBER',
      default_value => 1);

   sys.dbms_scheduler.enable(name => 'ProcessBackorders3');
END;
/

SELECT program_name, program_type, program_action, number_of_arguments
FROM dba_scheduler_programs
WHERE owner = 'UWCLASS';
 
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);
col owner format a6
col schedule_name format a25
col start_date format a35
col repeat_interval format a25

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

BEGIN
  dbms_scheduler.create_schedule('embed_sched', repeat_interval => 'FREQ=YEARLY;BYDATE=0130,0220,0725');

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

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

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

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

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

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

BEGIN
  dbms_scheduler.create_schedule('fifth_day_off', repeat_interval=> 'FREQ=retail_fiscal_year;BYDAY=SAT,SUN;BYPERIOD=2,4;BYSETPOS=5');
END;
/

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

BEGIN
  dbms_scheduler.drop_schedule('MAIN_SCHED');
  dbms_scheduler.drop_schedule('JOB2_SCHED');
  dbms_scheduler.drop_schedule('YEAR_START');
  dbms_scheduler.drop_schedule('RETAIL_FISCAL_YEAR');
  dbms_scheduler.drop_schedule('FIFTH_DAY_OFF');
  dbms_scheduler.drop_schedule('EMBED_SCHED');
END;
/

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;

BEGIN
  dbms_scheduler.create_schedule('PURGE_SCHED', REPEAT_INTERVAL=>'FREQ=HOURLY;BYHOUR=11,12,13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 00;BYMINUTE=25');
END;
/

SELECT owner, schedule_name, schedule_type, start_date, repeat_interval
FROM dba_scheduler_schedules;
 
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.

Overload 1		 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);
desc dba_scheduler_windows

col window_name format a16
col schedule_owner format a10
col next_start_date format a40

SELECT window_name, resource_plan, window_priority, next_start_date
FROM  dba_scheduler_windows;

BEGIN
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.create_plan('UW_PLAN', 'Sched Demo', 'RATIO');

  dbms_resource_manager.create_consumer_group('Workers', 'Those that do
actual work');

  dbms_resource_manager.create_plan_directive(plan=>'UW_PLAN',  group_or_subplan=>'Workers', comment=>'Can Grab All The CPU', cpu_p1=>100);

  dbms_resource_manager.create_plan_directive(plan=>'UW_PLAN', group_or_subplan=>'OTHER_GROUPS', comment=>'Testing', cpu_p2=>0);

  dbms_resource_manager.submit_pending_area;
END;
/

BEGIN
  dbms_scheduler.create_schedule('maint_sched',
  repeat_interval=> 'FREQ=YEARLY;BYDATE=0201^SPAN:1W;BYDAY=SUN');

  dbms_scheduler.create_window(
  window_name   => 'weeknights',
  resource_plan => 'UW_PLAN',
  schedule_name => 'maint_sched',
  duration      => INTERVAL '4' HOUR,
  window_priority => 'HIGH',
  comments => 'Off-hours maintenance window');
END;
/

SELECT window_name, resource_plan, window_priority, next_start_date
FROM  dba_scheduler_windows;

exec dbms_scheduler.drop_window('WEEKNIGHTS');
exec dbms_scheduler.drop_schedule('MAINT_SCHED');

BEGIN
  dbms_resource_manager.create_pending_area;
  dbms_resource_manager.delete_plan_cascade('UW_PLAN');
  dbms_resource_manager.submit_pending_area;
END;
/

select plan, group_or_subplan, mgmt_p1 from resource_plan_directive$;
Overload 2 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);
TBD
 
CREATE_WINDOW_GROUP
Creates a new window group dbms_scheduler.create_window_group(
group_name  IN VARCHAR2,
window_list IN VARCHAR2 DEFAULT NULL,
comments    IN VARCHAR2 DEFAULT NULL);
desc dba_scheduler_window_groups

col window_group_name format a24
col comments format a38

SELECT window_group_name, enabled, next_start_date, comments
FROM dba_scheduler_window_groups;

SELECT window_name
FROM dba_scheduler_windows;

BEGIN
  dbms_scheduler.create_window_group(
  group_name  => 'downtime',
  window_list => 'monday_window, wednesday_window, friday_window',
  comments => 'Group of system maintenance windows');
END;
/

SELECT window_group_name, enabled, next_start_date, comments
FROM dba_scheduler_window_groups;

SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;

exec dbms_scheduler.add_window_group_member('downtime', 'tuesday_window, thursday_window, saturday_window');

SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;

exec dbms_scheduler.remove_window_group_member('downtime', 'wednesday_window, thursday_window');

SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;

exec dbms_scheduler.drop_window_group('downtime');

SELECT window_group_name, number_of_windows
FROM dba_scheduler_window_groups;
 
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);
See CREATE_CHAIN Demo Above
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);
See CREATE_CHAIN Demo Above
 
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);
conn / as sysdba

GRANT create any job TO uwclass;

conn uwclass/uwclass

CREATE TABLE t (
col1 NUMBER,
col2 DATE);

BEGIN
  dbms_scheduler.create_program('MLPROG', 'plsql_block', 'INSERT INTO t VALUES (1, SYSDATE);
  user_lock.sleep(1);INSERT INTO uwclass.t VALUES (1, TRUE)');
END;
/

BEGIN
  dbms_scheduler.create_chain('CHN1', evaluation_interval=>interval '100' minute);
  dbms_scheduler.define_chain_step('CHN1', 'step1', 'MLPROG');
  dbms_scheduler.define_chain_rule('CHN1', '(select count(*) from uwclass.t)<10', 'START step1');
  dbms_scheduler.define_chain_rule('CHN1', ':STEP1.COMPLETED=''TRUE''', 'END');
  dbms_scheduler.enable('CHN1');
END;
/

exec dbms_scheduler.create_job('JOB1', 'chain', 'CHN1', 0, auto_drop=>TRUE, enabled=>TRUE);

col owner format a10
col status format a10
col run_duration format a15
col additional_info format a60

SELECT owner, status, run_duration, additional_info
FROM user_scheduler_job_run_details
WHERE job_name = 'JOB1';

col program_action format a40

SELECT program_name, program_type, program_action
FROM user_scheduler_programs;

exec dbms_scheduler.drop_chain('CHN1');
exec dbms_scheduler.drop_program('MLPROG');
exec dbms_scheduler.purge_log;
 
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);
 
Metadata Attributes
Type Data Type Description
event_message TIMESTAMP WITH TIMEZONE 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.
job_name VARCHAR2 Name of the currently running job
job_owner VARCHAR2 Owner 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.
window_end TIMESTAMP WITH TIMEZONE If the job was started by a window, the time that the window is scheduled to close
window_start TIMESTAMP WITH TIMEZONE If the job was started by a window, the time that the window opened
desc dba_scheduler_programs

col program_action format a50

SELECT program_name, program_type, program_action
FROM dba_scheduler_programs;

CREATE OR REPLACE PROCEDURE load_data(job_name VARCHAR2) IS
BEGIN
  NULL;
END load_data;
/

BEGIN
  dbms_scheduler.create_program(
  program_name   => 'Run_LOAD_DATA',
  program_type   => 'STORED_PROCEDURE',
  program_action => 'LOAD_DATA',
  number_of_arguments => 1,
  enabled        => FALSE,
  comments       => 'UW Test Scheduled Load');
END;
/

SELECT program_name, program_type, program_action
FROM dba_scheduler_programs;

exec dbms_scheduler.define_metadata_argument('Run_LOAD_DATA', 'JOB_NAME', 1);

exec dbms_scheduler.drop_program('Run_LOAD_DATA');
drop procedure load_data;
 
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 CREATE_PROGRAM Demos Above
 
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,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
See Scheduler Demo1 Below
 
DISABLE1_CALENDAR_CHECK
Undocumented import helper function dbms_scheduler.disable1_calendar_check;
exec dbms_scheduler.disable1_calendar_check;
 
DROP_AGENT_DESTINATION
??? dbms_scheduler.drop_agent_destination
xxx
 
DROP_CHAIN
Drop a chain dbms_scheduler.drop_chain(
chain_name IN VARCHAR2,
force      IN BOOLEAN DEFAULT FALSE);
See CREATE_CHAIN Demo Above
 
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);
See CREATE_CHAIN Demo Above
 
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);
See CREATE_CHAIN Demo Above
 
DROP_CREDENTIAL
Drops an existing credential (or a comma separated list of credentials) dbms_scheduler.drop_credential(
credential_name IN VARCHAR2,
force           IN BOOLEAN DEFAULT FALSE);
See CREATE_CREDENTIAL Demo Above
 
DROP_DATABASE_DESTINATION
Drops a database destination dbms_scheduler.drop_database_destination(destination_name IN VARCHAR2);
SELECT

BEGIN
  dbms_scheduler.create_database_destination('FINAPP_DEST' <agent>, 'ORABASE', 'MLib Demo');
  dbms_scheduler.drop_database_destination('FINAPP_DEST');
END;
/
 
DROP_FILE_WATCHER (new 11.2.0.1)
Creates a file watcher, which is a Scheduler object that defines the location, name, and other properties of a file whose arrival on a system causes the Scheduler to start a job dbms_scheduler.drop_file_watcher(
file_watcher_name IN VARCHAR2,
force             IN BOOLEAN DEFAULT FALSE);
See File Watcher Demo Below
 
DROP_GROUP
??? dbms_scheduler.drop_group(group_name IN VARCHAR2, force IN BOOLEAN DEFAULT FALSE);
xxx
 
DROP_JOB
Drop a job or several jobs dbms_scheduler.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');
See Scheduler Demo1 Below
 
DROP_JOB_CLASS
Drop a job class dbms_scheduler.drop_job_class(
job_class_name IN VARCHAR2,
force          IN BOOLEAN DEFAULT FALSE);
See CREATE_JOB_CLASS Demo Above
 
DROP_PROGRAM
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 Demo1 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 Demo1 Below
Overload 2 dbms_scheduler.drop_program_argument(
program_name  IN VARCHAR2,
argument_name IN VARCHAR2);
TBD
 
DROP_SCHEDULE
Drop a schedule (or comma-separated list of schedules) dbms_scheduler.drop_schedule(
schedule_name IN VARCHAR2,
force         IN BOOLEAN DEFAULT FALSE);
See CREATE_SCHEDULE and CREATE_WINDOW Demos
 
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);
See CREATE_WINDOW Demo
 
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);
See CREATE_WINDOW_GROUP Demo
 
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,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
See Scheduler Demo 1 Below
 
END_DETACHED_JOB_RUN
Undocumented dbms_scheduler.end_detached_job_run (
job_name        IN VARCHAR2,
error_number    IN PLS_INTEGER DEFAULT 0,
additional_info IN VARCHAR2 DEFAULT NULL);
TBD
 
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';

DECLARE
 start_date        TIMESTAMP;
 return_date_after TIMESTAMP;
 next_run_date     TIMESTAMP;
BEGIN
  start_date := TO_TIMESTAMP_TZ('01-JAN-2006 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;
/
 
EVALUTE_RUNNING_CHAIN
Forces immediate evaluation of a running chain dbms_scheduler.evaluate_running_chain(job_name IN VARCHAR2);
TBD
 
FILE_WATCH_FILTER
??? dbms_scheduler.file_watch_filter(
sch_name    IN VARCHAR2,
obj_name    IN VARCHAR2,
obj_subname IN VARCHAR2,
fw_msgid    IN RAW) RETURN NUMBER;
xxx
 
GENERATE_EVENT_LIST
Undocumented dbms_scheduler.generate_event_list(statusvec IN NUMBER) RETURN VARCHAR2
TBD
 
GENERATE_JOB_NAME
Returns a unique name for a job 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_AGENT_VERSION (new 11.2.0.1)
Returns the version of a Scheduler Execution Agent dbms_scheduler.get_agent_version(agent_host IN VARCHAR2) RETURN VARCHAR2;
desc dba_scheduler_db_dests

DECLARE
 retVal VARCHAR2(100);
BEGIN
  retVal := dbms_scheduler.get_agent_version('TEST');
  dbms_output.put_line(RetVal);
END;
/
DECLARE
*
ERROR at line 1:
ORA-27476: "SYS.TEST" does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4921
ORA-06512: at "SYS.DBMS_ISCHED", line 8619
ORA-29257: host TEST unknown
ORA-06512: at "SYS.DBMS_ISCHED", line 7415
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3998
ORA-06512: at line 4
 
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);
conn / as sysdba

set serveroutput on

DECLARE
 x BOOLEAN;
BEGIN
  dbms_scheduler.get_attribute('DEFAULT_JOB_CLASS', 'SYSTEM', x);
  IF x THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
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
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'. dbms_scheduler.get_default_value(attribute_name IN VARCHAR2)
RETURN VARCHAR2;
SELECT *
FROM dba_scheduler_global_attribute;

SELECT dbms_scheduler.get_default_value('LOG_HISTORY')
FROM dual;
 
GET_FILE
Retrieves a file from a specified destination host

Overload 1		 dbms_scheduler.get_file (
source_file     IN     VARCHAR2,
source_host     IN     VARCHAR2,
credential_name IN     VARCHAR2,
file_contents   IN OUT NOCOPY CLOB CHARACTER SET ANY_CS);
TBD
Overload 2 dbms_scheduler.get_file (
source_file     IN     VARCHAR2,
source_host     IN     VARCHAR2,
credential_name IN     VARCHAR2,
file_contents   IN OUT NOCOPY BLOB);
TBD
Overload 3 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);
TBD
 
GET_JOB_STEP_CF
Undocumented dbms_scheduler.get_job_step_cf (
iec   VARCHAR2,
icn   VARCHAR2,
vname VARCHAR2,
iev   SYS.RE$NV_LIST)
RETURN SYS.RE$VARIABLE_VALUE;
TBD
 
GET_SCHEDULER_ATTRIBUTE
Get the value of a scheduler attribute dbms_scheduler.get_scheduler_attribute(
attribute IN  VARCHAR2,
value     OUT VARCHAR2);
See SET_SCHEDULER_ATTRIBUTE Demo Below
 
GET_SYS_TIME_ZONE_NAME
Returns the current time zone setting dbms_scheduler.get_sys_time_zone_name RETURN VARCHAR2;
SELECT dbms_scheduler.get_sys_time_zone_name
FROM dual;
 
GET_VARCHAR2_VALUE
Converts SYS.ANYDATA to VARCHAR2 dbms_scheduler.get_varchar2_value(a IN SYS.ANYDATA) RETURN VARCHAR2;
CREATE TABLE t (mycol sys.anyData);

INSERT INTO t
VALUES (sys.anyData.convertVarchar2('hello world'));

SELECT * FROM t;

SELECT dbms_scheduler.get_varchar2_value(mycol)
FROM t;
 
IS_SCHEDULER_CREATED_AGENT (new 11.2.0.3)
Undocumented internal function dbms_scheduler.is_scheduler_created_agent(
schema_name VARCHAR2,
agent_name  VARCHAR2) RETURN BOOLEAN;
TBD
 
OPEN_WINDOW
Opens a window independent of its schedule dbms_scheduler.open_window(
window_name IN VARCHAR2,
duration    IN INTERVAL DAY TO SECOND,
force       IN BOOLEAN DEFAULT FALSE);
exec dbms_scheduler.open_window('weeknights', INTERVAL '4' HOUR);
 
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);
SELECT, COUNT(*)
FROM dba_scheduler_job_run_details;

exec dbms_scheduler.purge_log;

SELECT, COUNT(*)
FROM dba_scheduler_job_run_details;
 
PUT_FILE
Saves a file to one or more specified destination hosts

Overload 1		 dbms_scheduler.put_file(
destination_file        IN VARCHAR2,
destination_host        IN VARCHAR2,
credential_name         IN VARCHAR2,
file_contents           IN CLOB CHARACTER SET ANY_CS,
destination_permissions IN VARCHAR2 DEFAULT NULL);
TBD
Overload 2 dbms_scheduler.put_file(
destination_file        IN VARCHAR2,
destination_host        IN VARCHAR2,
credential_name         IN VARCHAR2,
file_contents           IN BLOB,
destination_permissions IN VARCHAR2 DEFAULT NULL);
TBD
Overload 3 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);
TBD
 
REGISTER_REMOTE_DATABASE
Undocumented dbms_scheduler.register_remote_database(
database_name IN VARCHAR2,
registered_as IN VARCHAR2,
database_link IN VARCHAR2 DEFAULT NULL,
replace       IN BOOLEAN  DEFAULT FALSE);
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);
See CREATE_WINDOW Demo
 
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);
exec dbms_scheduler.reset_job_argument_value('UW_File_Load', 2);
Overload 2 dbms_scheduler.reset_job_argument_value(
job_name      IN VARCHAR2,
argument_name IN VARCHAR2);
exec dbms_scheduler.reset_job_argument_value('UW_File_Load', 'YEARNO');
 
RESOLVE_CALENDAR_STRING
Undocumented

Overload 1		 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,
bymonthday       OUT BYLIST,
byyearday        OUT BYLIST,
byweekno         OUT BYLIST,
bymonth          OUT BYLIST);
TBD
 
RESOLVE_NAME
Retrieve the canonicalized object owner or name dbms_scheduler.resolve_name(
full_name     IN VARCHAR2,
default_owner IN VARCHAR2,
return_part   IN NUMBER)
RETURN VARCHAR2;
SELECT dbms_scheduler.resolve_name('SERVERS', 'UWCLASS', 1)
FROM dual;

SELECT dbms_scheduler.resolve_name('SERVERS', 'UWCLASS', 2)
FROM dual;
 
RUN_CHAIN
Immediately runs a job pointing to a chain starting with a list of specified steps. The job will be started in the background.

Overload 1		 dbms_scheduler.run_chain(
chain_name  IN VARCHAR2,
start_steps IN VARCHAR2,
job_name    IN VARCHAR2 DEFAULT NULL);
TBD
Immediately runs a job pointing to a chain starting with the given list of step states

Overload 2		 dbms_scheduler.run_chain(
chain_name      IN VARCHAR2,
step_state_list IN SYS.SCHEDULER$_STEP_TYPE_LIST,
job_name        IN VARCHAR2 DEFAULT NULL);
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
Run a job immediately dbms_scheduler.run_job(
job_name            IN VARCHAR2,
use_current_session IN BOOLEAN DEFAULT TRUE);

TRUE = synchronous, FALSE = asynchronous
See Scheduler Demo1 Below
 
SET_AGENT_REGISTRATION_PASS
Set the remote execution agent registration password for this database optionally limit the password to a limited number of uses or to before a specified expiry date dbms_scheduler.set_agent_registration_pass(
registration_password IN VARCHAR2,
expiration_date       IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
max_uses              IN PLS_INTEGER DEFAULT NULL);
BEGIN
  dbms_scheduler.set_agent_registration_pass('N0!', SYSTIMESTAMP+1, 3);
END;
/

SELECT owner, object_name, object_type
FROM dba_objects_ae
WHERE created > SYSDATE-1
AND object_name NOT LIKE 'W%';
 
SET_ATTRIBUTE
Sets an attribute of a scheduler object

Overload 1		 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN BOOLEAN);
dbms_scheduler.set_attribute('JOB1', 'restartable', TRUE);
Overload 2 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN VARCHAR2,
value2    IN VARCHAR2 DEFAULT NULL);
TBD
Overload 3 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN DATE);
TBD
Overload 4 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN TIMESTAMP);
TBD
Overload 5 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN TIMESTAMP WITH TIME ZONE);
TBD
Overload 6 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN TIMESTAMP WITH LOCAL TIME ZONE);
TBD
Overload 7 dbms_scheduler.set_attribute(
name      IN VARCHAR2,
attribute IN VARCHAR2,
value     IN INTERVAL DAY TO SECOND);
TBD
 
SET_ATTRIBUTE_NULL
Sets an attribute of a scheduler program to NULL dbms_scheduler.set_attribute_null(
name      IN VARCHAR2,
attribute IN VARCHAR2);
exec dbms_scheduler.set_attribute_null('JOB1', 'restartable');
 
SET_JOB_ANYDATA_VALUE
Set a value to be passed to one of the arguments of a program using the ANYDATA data type

Overload 1		 dbms_scheduler.set_job_anydata_value(
job_name          IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value    IN SYS.ANYDATA);
TBD
Overload 2 dbms_scheduler.set_job_anydata_value(
job_name       IN VARCHAR2,
argument_name  IN VARCHAR2,
argument_value IN SYS.ANYDATA);
TBD
 
SET_JOB_ARGUMENT_VALUE
Set a value to be passed to one of the arguments of the program

Overload 1		 dbms_scheduler.set_job_argument_value(
job_name          IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value    IN VARCHAR2);
exec dbms_scheduler.set_job_argument_value('UW_File_Load', 2, '2007');
Overload 2 dbms_scheduler.set_job_argument_value(
job_name       IN VARCHAR2,
argument_name  IN VARCHAR2,
argument_value IN VARCHAR2);
exec dbms_scheduler.set_job_argument_value('UW_File_Load', 'YEARNO', '2009');
 
SET_JOB_ATTRIBUTES
Batch set job attribute dbms_scheduler.set_job_attributes(
jobattr_array    IN SYS.JOBATTR_ARRAY,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
TBD
 
SET_SCHEDULER_ATTRIBUTE
Set the value of a scheduler attribute. This takes effect immediately, but the resulting changes may not be seen immediately. dbms_scheduler.set_scheduler_attribute(
attribute IN VARCHAR2,
value     IN VARCHAR2);
set serveroutput on

DECLARE
 x VARCHAR2(100);
BEGIN
  dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
  dbms_output.put_line('DTZ: ' || x);
  dbms_scheduler.get_scheduler_attribute('EVENT_EXPIRY_TIME', x);
  dbms_output.put_line('EET: ' || x);
  dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
  dbms_output.put_line('LH: ' || x);
  dbms_scheduler.get_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', x);
  dbms_output.put_line('MJSP: ' || x);
END;
/

SELECT DISTINCT tzname
FROM v$timezone_names
WHERE tzname LIKE 'US%';

BEGIN
  dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 3);
  dbms_scheduler.set_scheduler_attribute('DEFAULT_TIMEZONE', 'US/Pacific';
END;
/

DECLARE
 x VARCHAR2(100);
BEGIN
  dbms_scheduler.get_scheduler_attribute('DEFAULT_TIMEZONE', x);
  dbms_output.put_line('DTZ: ' || x);
  dbms_scheduler.get_scheduler_attribute('EVENT_EXPIRY_TIME', x);
  dbms_output.put_line('EET: ' || x);
  dbms_scheduler.get_scheduler_attribute('LOG_HISTORY', x);
  dbms_output.put_line('LH: ' || x);
  dbms_scheduler.get_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', x);
  dbms_output.put_line('MJSP: ' || x);
END;
/
 
SHOW_ERRORS
Batch show errors dbms_scheduler.show_errors(error_list OUT SYS.SCHEDULER$_BATCHERR_ARRAY);
TBD
 
STIME
Get scheduler default time and timezone dbms_scheduler.stime(follow_default_timezone BOOLEAN DEFAULT FALSE)
RETURN TIMESTAMP WITH TIME ZONE;
SELECT dbms_scheduler.stime
FROM dual;
 
STOP_JOB
Stop a job or several jobs that are currently running dbms_scheduler.stop_job(
job_name         IN VARCHAR2,
force            IN BOOLEAN  DEFAULT FALSE,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
See Scheduler Demo1 Below
 
SUBMIT_REMOTE_EXTERNAL_JOB (new 11.2.0.3 parameter)
Execution engine for remote external jobs dbms_scheduler.submit_remote_external_job(
job_name            IN VARCHAR2,
job_subname         IN VARCHAR2,
job_owner           IN VARCHAR2,
command             IN VARCHAR2,
arguments           IN ODCIVARCHAR2LIST,
credential_name     IN VARCHAR2,
credential_owner    IN VARCHAR2,
destination         IN VARCHAR2,
destination_owner   IN VARCHAR2,
destination_name    IN VARCHAR2,
job_dest_id         IN VARCHAR2,
job_action          IN VARCHAR2,
job_scheduled_start IN TIMESTAMP WITH TIME ZONE,
job_start           IN TIMESTAMP WITH TIME ZONE,
window_start        IN TIMESTAMP WITH TIME ZONE,
window_end          IN TIMESTAMP WITH TIME ZONE,
chainid             IN VARCHAR2,
request_id          IN NUMBER,
log_id              IN NUMBER);
TBD
 
UNREGISTER_REMOTE_DATABASE
Undocumented dbms_scheduler.unregister_remote_database(
database_name IN VARCHAR2,
registered_as IN VARCHAR2);
TBD
 
Scheduler Demos
Demo 1: Job Based on Stored Procedure CREATE TABLE t (
valcol NUMBER,
insdat DATE);

CREATE OR REPLACE PROCEDURE load_data (inval IN NUMBER) IS
BEGIN
  FOR i IN 1..3 LOOP
    user_lock.sleep(100);
    INSERT INTO t VALUES (inval, SYSDATE);
  END LOOP;
END load_data;
/

DECLARE
  jname VARCHAR2(30);
BEGIN
  dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2);
  FOR i IN 1 .. 100 LOOP
    jname := 'JOB' || TO_CHAR(i);

    dbms_scheduler.create_job(job_name=>jname, job_type=>'STORED_PROCEDURE', job_action=>'LOAD_DATA', number_of_arguments=>1, enabled=>FALSE, auto_drop=>TRUE);

    dbms_scheduler.set_job_argument_value(job_name=>jname, argument_position=>1, argument_value=>i);

    dbms_scheduler.enable(jname);
  END LOOP;
END spawn_jobs;
/

SELECT * FROM t;

/

/

/
Demo 2: Job Based on Stored Procedure conn / as sysdba

GRANT create any directory TO uwclass;
GRANT create procedure TO uwclass;
GRANT create table TO uwclass;

GRANT create job TO uwclass;
GRANT manage scheduler TO uwclass;

conn uwclass/uwclass

set linesize 121

-- create directory
CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';

-- create load files
-- 01012008.dat
20046,32.83732,-96.80432
20056,32.58215,-97.35639
20057,32.85561,-97.24912
20058,32.81017,-96.96251
20060,32.85566,-97.25105

-- 01022008.dat
20061,32.85367,-97.24873
20063,32.85435,-97.24535
20064,32.85414,-97.24550
20065,32.75283,-97.25089
20066,32.84538,-96.97260

-- 01032008.dat
19882,32.83566,-96.96873
19898,32.83508,-96.93626
19900,32.86650,-97.24690
19915,32.81678,-96.95659
19816,32.83405,-96.97099

-- 01042008.dat
19817,32.83386,-96.97072
19818,32.95360,-96.99048
19820,32.81636,-96.97261
19833,32.82940,-96.97367
19836,32.83260,-96.94896

-- 01052008.dat
19837,32.87159,-97.24725
19839,32.83503,-96.93805
19841,32.86650,-97.24690
19843,32.86424,-97.24691
19844,32.83270,-96.97369

-- create table
CREATE TABLE locations (
location_id NUMBER(10),
latitude    FLOAT(20),
longitude   FLOAT(20));

-- create stored procedure
CREATE OR REPLACE PACKAGE sched_demo IS
runno  PLS_INTEGER := 0;
PROCEDURE load_data(fname VARCHAR2, yearno PLS_INTEGER);
END sched_demo;
/

CREATE OR REPLACE PACKAGE BODY sched_demo IS

PROCEDURE load_data(fname VARCHAR2, yearno PLS_INTEGER) IS
 vSFile   utl_file.file_type;
 vNewLine VARCHAR2(200);
 p1       PLS_INTEGER;
 p2       PLS_INTEGER;
 locid    locations.location_id%TYPE;
 latit    locations.latitude%TYPE;
 longi    locations.longitude%TYPE;
BEGIN
  vSFile := utl_file.fopen('CTEMP', fname || TO_CHAR(yearno) ||
  '.dat', 'R');
  LOOP
    BEGIN
      utl_file.get_line(vSFile, vNewLine);
      IF vNewLine IS NULL THEN
        EXIT;
      END IF;

      p1 := INSTR(vNewLine,',',1,1);
      p2 := INSTR(vNewLine,',',1,2);
      locid := SUBSTR(vNewLine, 1, p1-1);
      latit := SUBSTR(vNewLine, p1+1, p2-p1-1);
      longi := SUBSTR(vNewLine, 15+1);

      INSERT INTO locations
      (location_id, latitude, longitude)
      VALUES
      (locid, latit, longi);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;
    END;
  END LOOP;
  COMMIT;
  utl_file.fclose(vSFile);

  runno := runno + 1;
  utl_file.frename('CTEMP',fname || TO_CHAR(yearno) || '.dat',
  'CTEMP', TO_CHAR(runno) || '.arc', TRUE);
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END load_data;

END sched_demo;
/

-- test procedure
exec sched_demo.load_data('0101', 2008);

SELECT * FROM locations;

TRUNCATE TABLE locations;

-- rename 1.arc back to 01012008.dat

-- create a program with all job arguments
-- requires create job privilege
BEGIN
  dbms_scheduler.create_program(
  program_name   => 'Run_LOAD_DATA',
  program_type   => 'STORED_PROCEDURE',
  program_action => 'SCHED_DEMO.LOAD_DATA',
  number_of_arguments => 2,
  enabled        => FALSE,
  comments       => 'UW Test Scheduled Load');
END;
/

desc all_scheduler_programs

col owner format a10
col program_name format a25
col program_action format a45
col comments format a55

SELECT owner, program_name, program_type, program_action
FROM all_scheduler_programs;

SELECT owner, program_name, enabled, comments
FROM all_scheduler_programs;

-- set program argument
SELECT overload, position, argument_name, data_type
FROM all_arguments
WHERE object_name = 'SCHED_DEMO.LOAD_DATA';

BEGIN
  dbms_scheduler.define_program_argument(
  program_name      => 'Run_LOAD_DATA',
  argument_position => 1,
  argument_type     => 'VARCHAR2',
  default_value     => '0101');

  dbms_scheduler.define_program_argument(
  program_name      => 'Run_LOAD_DATA',
  argument_position => 2,
  argument_type     => 'NUMBER',
  default_value     => 2007);
END;
/

desc all_scheduler_job_args

col job_name format a15
col argument_type format a20
col default_value format a20

SELECT program_name, argument_name, argument_position, argument_type, default_value
FROM all_scheduler_program_args;

-- create job
BEGIN
  dbms_scheduler.create_job(
  job_name => 'UW_File_Load',
  program_name => 'Run_LOAD_DATA',
  start_date => dbms_scheduler.stime,
  repeat_interval => 'FREQ=MINUTELY;INTERVAL=1',
  end_date => dbms_scheduler.stime+1,
  enabled => FALSE,
  auto_drop => FALSE,
  comments => 'UW Demo Job');
END;
/

desc all_scheduler_jobs

col start_date format a40

SELECT job_name, program_name, start_date
FROM all_scheduler_jobs;

-- set scheduler attributes
col value format a50

SELECT attribute_name, value
FROM all_scheduler_global_attribute;

-- requires manage scheduler privilege
BEGIN
  dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 2);
END;
/

SELECT attribute_name, value
FROM all_scheduler_global_attribute;

-- enable the program
exec dbms_scheduler.enable('Run_LOAD_DATA');
-- enable the job
exec dbms_scheduler.enable('UW_File_Load');>

SELECT * FROM locations;

-- test the job
exec dbms_scheduler.run_job('UW_File_Load', TRUE);

SELECT * FROM locations;

col additional_info format a25

SELECT job_name, operation, status, additional_info
FROM all_scheduler_job_log
WHERE owner = 'UWCLASS';

SELECT job_name, state, run_count, next_run_date
FROM all_scheduler_jobs;

-- watch the job run renaming files as required to avoid a conflict

-- clean up
BEGIN
  -- stop the job
  BEGIN
    dbms_scheduler.stop_job('UW_File_Load', TRUE);
  EXCEPTION
    WHEN OTHERS THEN
      NULL;
  END;
  -- drop program argument
  dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 1);
  dbms_scheduler.drop_program_argument('Run_LOAD_DATA', 2);
  -- disable the program
  dbms_scheduler.disable('Run_LOAD_DATA', TRUE);
  -- drop the program
  dbms_scheduler.drop_program('Run_LOAD_DATA', TRUE);
  -- drop the job
  dbms_scheduler.drop_job('UW_File_Load', TRUE);
END;
/
Scheduled External Job Demo 3 -- if the job is not owned by SYS then edit $ORACLE_HOME/rdbms/admin and
-- edit the file externaljob.ora make run_user=oracle and run_group=dba

conn / as sysdba

BEGIN
  dbms_scheduler.create_job(
  job_name   => 'EXT_LOAD',
  job_type   => 'EXECUTABLE',
  job_action => 'c:\oracle\product\11.2.0\db_1\bin\sqlldr.exe',
  number_of_arguments => 1,
  start_date => SYSTIMESTAMP,
  enabled    => FALSE,
  comments   => 'SQL*Loader Job Demo');
END;
/

BEGIN
  dbms_scheduler.set_job_argument_value('EXT_LOAD', 1, argument_value=>'userid=uwclass/uwclass control=c:\temp\sqlldr02.ctl log=c:\temp\sqlldr02.log');
END;
/

exec dbms_scheduler.enable('EXT_LOAD');

col status format a10
col additional_info format a80

SELECT job_name, operation, status
FROM all_scheduler_job_log
WHERE owner = 'SYS';

SELECT job_name, additional_info
FROM all_scheduler_job_run_details;

exec dbms_scheduler.disable('EXT_LOAD');

exec dbms_scheduler.drop_job('EXT_LOAD');
File Watcher Demo conn / as sysdba

CREATE OR REPLACE DIRECTORY stage AS '/stage';

GRANT read, write ON DIRECTORY stage TO uwclass;

GRANT execute ON sys.scheduler_filewatcher_result TO uwclass;

--GRANT create job TO uwclass;
--GRANT manage scheduler TO uwclass;
--GRANT execute ON dbms_lock to watcher_user;
--GRANT execute ON dbms_system to watcher_user;

conn uwclass/uwclass

CREATE TABLE t_staging_files(
upload_timestamp TIMESTAMP,
file_name        VARCHAR2(30),
file_size        NUMBER,
contents         CLOB);

CREATE OR REPLACE PROCEDURE load_file(payload IN sys.scheduler_filewatcher_result)
AUTHID DEFINER IS
 l_clob       CLOB;
 l_bfile      BFILE;
 dest_offset  INTEGER := 1;
 src_offset   INTEGER := 1;
 src_csid     NUMBER  := NLS_CHARSET_ID('AL32UTF8');
 lang_context INTEGER := dbms_lob.default_lang_ctx;
 warning      INTEGER;
BEGIN
  INSERT INTO t_staging_files (UPLOAD_TIMESTAMP) VALUES (SYSTIMESTAMP);
  COMMIT;

  INSERT INTO t_staging_files
  (upload_timestamp , file_name, file_size, contents)
  VALUES
  (payload.file_timestamp, payload.directory_path || '/' || payload.actual_file_name,
   payload.file_size, empty_clob()) RETURNING contents INTO l_clob;

  l_bfile := bfilename('STAGE', payload.actual_file_name);

  dbms_lob.fileopen(l_bfile);

  dbms_lob.loadclobfromfile(l_clob, l_bfile, dbms_lob.getlength(l_bfile),
  dest_offset, src_offset, src_csid, lang_context, warning);

  dbms_lob.fileclose(l_bfile);
  commit;
END load_file;
/

desc dba_scheduler_credentials

SELECT COUNT(*)
FROM dba_scheduler_credentials;

-- create job credential
exec dbms_scheduler.create_credential('uw_credential', 'uwclass', 'uwclass');

SELECT owner, credential_name, username
FROM dba_scheduler_credentials;

desc dba_scheduler_programs

col program_name format a25
col program_action format a50

SELECT program_name, program_type, program_action, number_of_arguments, enabled
FROM dba_scheduler_programs
ORDER BY 1;

-- create program in disabled state
exec dbms_scheduler.create_program('file_watcher', 'stored_procedure', 'load_file', 1);

SELECT program_name, program_type, program_action, number_of_arguments, enabled
FROM dba_scheduler_programs
ORDER BY 1;

desc dba_scheduler_program_args

col argument_type format a15

SELECT argument_name, argument_position, argument_type, metadata_attribute
FROM dba_scheduler_program_args
WHERE program_name = 'FILE_WATCHER';

  -- define program argument
exec dbms_scheduler.define_metadata_argument('file_watcher', 'EVENT_MESSAGE', 1);

SELECT argument_name, argument_position, argument_type, metadata_attribute
FROM dba_scheduler_program_args
WHERE program_name = 'FILE_WATCHER';

SELECT program_name, enabled
FROM dba_scheduler_programs
ORDER BY 1;

-- enable program
exec dbms_scheduler.enable('file_watcher');

SELECT program_name, enabled
FROM dba_scheduler_programs
ORDER BY 1;

desc dba_scheduler_file_watchers

SELECT file_watcher_name, enabled, directory_path, file_name, credential_name
FROM dba_scheduler_file_watchers;

-- create file watcher
exec dbms_scheduler.create_file_watcher('UW_FWatch', 'STAGE', 'democlob.txt', 'uw_credential');

SELECT file_watcher_name, enabled, directory_path, file_name, credential_name
FROM dba_scheduler_file_watchers;

desc dba_scheduler_jobs

col job_action format a20
col file_watcher_name format a20
col start_date format a20

SELECT job_name, program_name, file_watcher_name, job_type, job_action
FROM dba_scheduler_jobs
ORDER BY program_name NULLS FIRST;

  -- create job (overload 5)
exec dbms_scheduler.create_job('UW_File_Job', 'file_watcher', event_condition => 'tab.user_data.file_size > 10', queue_spec => 'UW_FWatch', enabled => TRUE, auto_drop => FALSE);

exec dbms_scheduler.set_attribute('UW_File_Job', 'parallel_instances', TRUE);

/*
  -- enable the program
exec dbms_scheduler.enable('file_watcher');
*/

exec dbms_scheduler.run_job('UW_FILE_JOB');

*FAILED*
  -- change interval
  dbms_scheduler.set_attribute('UW_File_Job', 'repeat_interval', 'FREQ=MINUTELY;INTERVAL=2');
END;
/

-- copy the demo file to c:\temp\test.txt
col directory_path format a10
col file_name format a15

SELECT file_watcher_name, directory_path, file_name, credential_name, enabled
FROM dba_scheduler_file_watchers;

SELECT * FROM t_staging_files;

-- tear it down
BEGIN
  dbms_scheduler.drop_file_watcher('UW_Fwatch', TRUE);
  dbms_scheduler.drop_program('file_watcher');
  dbms_scheduler.drop_credential('uw_credential', TRUE);
END;
/
 
Related Topics
DBMS_AUTOTASK_ADMIN
DBMS_ISCHED
DBMS_JOB
DBMS_LOB
DBMS_RESOURCE_MANAGER
DBMS_RULE_ADM
Directories
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved