PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


 
 Search the Reference Library pages:  

Oracle DBMS_SCHEDULER
Version 11.1
 
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
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';

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;
Security Model Execute is granted to PUBLIC with GRANT OPTION
-- which is just plain stupid and should be revoked upon installation.

AUTHID CURRENT_USER

Security Privileges
Privilege Description
Create Any Job 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;
/

 
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_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 (new 11g overload)

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');
 
CONVERT_DBMS_JOB (new 11g)

Convert a dbms_job api created job into a scheduler job the dbms_job equivalent job will be removed.

As this is officially undocumented I am leaving this as it is until I learn more.

 dbms_scheduler.convert_dbms_job(job_name IN VARCHAR2);

Step 1: Create a job

CREATE OR REPLACE PROCEDURE test_job IS
BEGIN
  NULL;
END test_job;
/

DECLARE
 JobNo user_jobs.job%TYPE;
BEGIN
  dbms_job.submit(JobNo, 'begin test_job; end;', SYSDATE,
  'SYSDATE + 36/86400');
  COMMIT;
END;
/

SELECT job, schema_user
FROM dba_jobs;

Step 2: Convert it

-- This might actually work if DBMS_JOB created jobs had VARCHAR2 names.
-- Unfortunately they do not so I can not puzzle this one out unless I 
-- assume the information in the rdbms/admin file is incorrect.
 
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 (new 11g)

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', 'oracle', 'oracle1');
END;
/

SELECT owner, credential_name, username
FROM dba_scheduler_credentials;

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);
 
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);
 
CREATE_JOB (new 11g parameter in overloads 3 and 4)

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

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,
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);
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');
TBD

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');
See Scheduler Demo1 Below

Create a job using 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');
TBD

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);
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_JOBS (new 11.1.0.6 with new 11.1.0.7 overload)

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		 dbms_scheduler.create_jobs(
job_array        IN SYS.JOB_ARRAY,
commit_semantics IN VARCHAR2 DEFAULT 'STOP_ON_FIRST_ERROR');
TBD
 
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 Demo1 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);
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;
 
CREATE_WINDOW (new 11g overload)

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);
See CREATE_CHAIN Demo Above
 
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);
TBD
 
DISABLE (new 11g parameter)
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;
dbms_scheduler.disable1_calendar_check;
 
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 (new 11g)

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_JOB (new 11g parameter)
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 (new 11g parameter)
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 Demo1 Below
 
END_DETACHED_JOB_RUN (new 11g)
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
 
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_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 (new 11g)

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;
 
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 (new 11g)

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 (new 11g)

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 (new 11g)

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
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 (new 11g)

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

BEGIN
  dbms_scheduler.set_scheduler_attribute('MAX_JOB_SLAVE_PROCESSES', 3);
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 (new 11g)
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 (new 11g parameter)
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 11g)

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,
request_id       IN NUMBER);
TBD
 
UNREGISTER_REMOTE_DATABASE (new 11g)

Undocumented		 dbms_scheduler.unregister_remote_database(
database_name IN VARCHAR2,
registered_as IN VARCHAR2);
TBD
 
Scheduler Demos

Demo 1: 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: emp';

-- 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		 -- 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 .1.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: emp\sqlldr02.ctl log=c: emp\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');
 
Related Topics
DBMS_JOB
DBMS_RESOURCE_MANAGER
DBMS_RULE_ADM
 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us    © 2003 - 2024 psoug.org
-----