I've been trying to create a Scheduler Chain in Oracle 11.2 to kick off steps based off of a schedule. Below is an example script I used to attempt this, what I would like is Step1 to execute with the start of the chain, then Step2 to execute on a schedule, then Step3 to execute once Step1 and Step2 are complete. This is for a nightly process where Step1 would execute at midnight, then Step2 would execute at 1AM. The step I'm having issues with is defining the start of Step2 is at line 134.
I also couldn't find an example of using DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP using the event_schedule_name overload, so if anyone has come across one please share.
/* CREATE TABLE */
Create table chain_table
(
chain_ts timestamp default current_timestamp,
chain_step varchar2(100)
);
/* CREATE PROCEDURE */
create procedure chain_test_proc
(
step_name in varchar2
) as
begin
execute immediate 'insert into chain_table (chain_step) values (:1)'
using step_name;
commit;
end chain_test_proc;
/
/* CREATE SCHEDULE */
begin
dbms_scheduler.create_schedule
(
repeat_interval => 'FREQ=MINUTELY;',
start_date => to_timestamp_tz('2014-03-17 12:14:00.000000000 AMERICA/CHICAGO','YYYY-MM-DD HH24:MI:SS.FF TZR'),
comments => 'Runs minutely for testing',
schedule_name => '"STEP2_SCHEDULE"');
end;
/
/* CREATE PROGRAMS */
begin
dbms_scheduler.create_program
(
program_name => 'STEP1_PROGRAM',
program_action => 'CHAIN_TEST_PROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => null,
enabled => false
);
dbms_scheduler.define_program_argument
(
program_name => 'STEP1_PROGRAM',
argument_position => 1,
argument_name => 'STEP_NAME',
argument_type => 'VARCHAR2',
default_value => 'STEP1',
out_argument => false
);
dbms_scheduler.create_program
(
program_name => 'STEP2_PROGRAM',
program_action => 'CHAIN_TEST_PROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => null,
enabled => false
);
dbms_scheduler.define_program_argument
(
program_name => 'STEP2_PROGRAM',
argument_position => 1,
argument_name => 'STEP_NAME',
argument_type => 'VARCHAR2',
default_value => 'STEP2',
out_argument => false
);
dbms_scheduler.create_program
(
program_name => 'STEP3_PROGRAM',
program_action => 'CHAIN_TEST_PROC',
program_type => 'STORED_PROCEDURE',
number_of_arguments => 1,
comments => null,
enabled => false
);
dbms_scheduler.define_program_argument
(
program_name => 'STEP3_PROGRAM',
argument_position => 1,
argument_name => 'STEP_NAME',
argument_type => 'VARCHAR2',
default_value => 'STEP3',
out_argument => false
);
dbms_scheduler.enable ('STEP1_PROGRAM');
dbms_scheduler.enable ('STEP2_PROGRAM');
dbms_scheduler.enable ('STEP3_PROGRAM');
end;
/
begin
/* CREATE CHAIN */
sys.dbms_scheduler.create_chain
(
chain_name => 'MY_CHAIN',
rule_set_name => null,
evaluation_interval => null,
comments => null
);
/* CREATE CHAIN STEPS */
sys.dbms_scheduler.define_chain_step
(
chain_name => 'MY_CHAIN',
step_name => 'STEP1_PROG',
program_name => 'STEP1_PROGRAM'
);
sys.dbms_scheduler.define_chain_step
(
chain_name => 'MY_CHAIN',
step_name => 'STEP2_PROG',
program_name => 'STEP2_PROGRAM'
);
sys.dbms_scheduler.define_chain_event_step
(
chain_name => 'MY_CHAIN',
step_name => 'STEP2_SCHED',
event_schedule_name => 'STEP2_START_SCHEDULE'
);
sys.dbms_scheduler.define_chain_step
(
chain_name => 'MY_CHAIN',
step_name => 'STEP3_PROG',
program_name => 'STEP3_PROGRAM'
);
/* CREATE CHAIN RULES */
-- Start of Chain
sys.dbms_scheduler.define_chain_rule
(
chain_name => 'MY_CHAIN',
condition => 'TRUE',
action => 'START STEP1_PROG',
rule_name => 'STEP1',
comments => null
);
-- Step having issues with
sys.dbms_scheduler.define_chain_rule
(
chain_name => 'MY_CHAIN',
condition => 'STEP2_SCHED COMPLETED',
action => 'START STEP2_PROG',
rule_name => 'STEP2',
comments => 'Runs on a schedule'
);
-- Last step
sys.dbms_scheduler.define_chain_rule
(
chain_name => 'MY_CHAIN',
condition => 'STEP1_PROG COMPLETED AND STEP2_PROG COMPLETED',
action => 'START STEP3_PROG',
rule_name => 'STEP3',
comments => null
);
-- End Chain
sys.dbms_scheduler.define_chain_rule
(
chain_name => 'MY_CHAIN',
condition => 'STEP3_PROG COMPLETED',
action => 'END',
rule_name => 'END_CHAIN',
comments => null
);
/* ENABLE CHAIN */
dbms_scheduler.enable (name => 'MY_CHAIN');
end;
/
/* RUN CHAIN */
-- exec dbms_scheduler.run_chain (chain_name => 'MY_CHAIN', start_steps => null, job_name => null);
/* ROLLBACK
-- Drop Chain
exec dbms_scheduler.drop_chain ('MY_CHAIN');
-- Drop Programs
exec dbms_scheduler.drop_program ('STEP1_PROGRAM, STEP2_PROGRAM, STEP3_PROGRAM');
-- Drop Schedule
exec dbms_scheduler.drop_schedule ('STEP2_SCHEDULE');
-- Drop table
drop table chain_table;
-- Drop procedure
drop procedure chain_test_proc;
*/