Hello All,
I have created a chain that is supposed to run 3 programs daily and the second two should not run until the first one completes successfully however my chain never seems to get beyond step 1. Here is my code for creating the chain...
begin
dbms_scheduler.drop_chain('daily_chain1',TRUE);
dbms_scheduler.create_chain (
chain_name => 'daily_chain1',
rule_set_name => null,
evaluation_interval => null,
comments => 'chain for daily jobs');
end;
begin
dbms_scheduler.create_program('prg_person_merge','stored_procedure','p_person_merge');
dbms_scheduler.create_program('prg_aloc_flat','stored_procedure','p_aloc_flat');
dbms_scheduler.create_program('prg_legal_episodes','stored_procedure','get_all_legal_episodes');
end;
begin
dbms_scheduler.enable('prg_person_merge');
dbms_scheduler.enable('prg_aloc_flat');
dbms_scheduler.enable('prg_legal_episodes');
end;
begin
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('daily_chain1', 'step1', 'prg_person_merge');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('daily_chain1', 'step2', 'prg_aloc_flat');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('daily_chain1', 'step3', 'prg_legal_episodes');
end;
begin
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('daily_chain1','TRUE','START step1', 'chain1_rule1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('daily_chain1', 'step1 SUCCEEDED', 'Start step2', 'chain1_rule2');
dbms_scheduler.define_chain_rule('daily_chain1','step1 not succeeded','END' ,'chain1_rule3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('daily_chain1' ,'step2 COMPLETED', 'start step3','chain1_rule4');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('daily_chain1' ,'step3 COMPLETED', 'END','chain1_rule5');
end;
begin
DBMS_SCHEDULER.DROP_JOB('J_DAILY_CHAIN');
DBMS_SCHEDULER.create_job (
job_name => 'J_DAILY_CHAIN',
job_type => 'CHAIN',
job_action => 'KELLYN.DAILY_CHAIN1',
start_date => TO_DATE(20111206070000,'YYYYMMDDHH24MISS'),
repeat_interval => 'FREQ = DAILY',
end_date => NULL,
enabled => TRUE,
comments => 'JOB RUNS DAILY CHAIN');
DBMS_SCHEDULER.ENABLE('daily_chain1');
END;
but this is what I get in the log...
LOG_ID LOG_DATE OWNER JOB_NAME JOB_SUBNAME STATUS ERROR# REQ_START_DATE ACTUAL_START_DATE RUN_DURATION INSTANCE_ID SESSION_ID SLAVE_PID CPU_USED ADDITIONAL_INFO
13650 12/5/2011 7:00:02.204509 AM -06:00 KELLYN J_DAILY_CHAIN SUCCEEDED 0 12/5/2011 7:00:00.500000 AM -06:00 12/5/2011 7:00:00.947443 AM -06:00 +00 00:00:01.000000 1 107,25724 1095 +00 00:00:00.000000 CHAIN_LOG_ID="13649"
According to the oracle documentation, each step should have it's own entry in the log with a unique subname so it appears as if the steps are not "executing" and the objects that the steps, programs and procedures are using are unchanged. I do not know what to try next as I have no discernable error messages that I can find. I am using 10gr2 via TOAD
Edited by: user634162 on Dec 5, 2011 9:14 AM