Hi, all.
I'm hoping for some help from the community again.
There's a chain created (code below) and it's running successfully -- at least it's building MOST tables.
The problem is: the last three tables are all dependent on the same condition and only two of them run.
From the code below, step 4, step 5 and step 6 are all dependent on the success of step 3.
When the job for this chain is run, all the steps run successfully except for step 5.
The interesting thing (to me at least) is that whichever table build is step 5 fails. For example, in the code below prog_Y is assigned to step 5 and it fails. If I swap prog_Y to step_4 and prog_X to step_5, step_5 still fails. Same result if prog_Z is listed as step 5. While steps 4 and 6 run successfully.
The additional info in job run details lists: table or view does not exist. However, I've confirmed the procs behind the programs each run successfully and the tables do exist.
I've tried several things to get more info: changing the step numbers.dropping/redefining step, dropping chain and redefining, retyping the steps and rules.
Please help. Any suggestions of why this step 5 might be failing or where to look for more information is super helpful.
Thanks so much
Fritz
When the chain is run partially
/* 1. Create Chain */
BEGIN
dbms_scheduler.create_chain (
CHAIN_NAME => 'dgr_cmpl_chain',
RULE_SET_NAME => null,
EVALUATION_INTERVAL => null,
comments => 'Chain which loads the degree completion tables.');
END;
/* 2. Define the steps in the chain */
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_1',
program_name => 'prog_intrmdt_1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_2',
program_name => 'prog_intrmdt_2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_3',
program_name => 'prog_base');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_4',
program_name => 'prog_X');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_5',
program_name => 'prog_Y');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'dgr_cmpl_chain',
step_name => 'step_6',
program_name => 'prog_Z');
END;
/* 3. Add rules */
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'TRUE',
action => 'START step_1',
rule_name => 'rule_1',
comments => 'Start Degree Completion chain.');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_1 succeeded',
action => 'START step_2',
rule_name => 'rule_2');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_2 succeeded',
action => 'START step_3',
rule_name => 'rule_3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_3 succeeded',
action => 'START step_4',
rule_name => 'rule_4');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_3 succeeded',
action => 'START step_5',
rule_name => 'rule_5');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_3 succeeded',
action => 'START step_6',
rule_name => 'rule_6');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'dgr_cmpl_chain',
condition => 'step_4 completed and step_5 completed and step_6 completed',
action => 'END',
rule_name => 'rule_7');
END;
/*
*/
/* 4. Enable the chain */
BEGIN
DBMS_SCHEDULER.ENABLE ('dgr_cmpl_chain');
END;
/* 5. Create a job that points to the chain. */
/*Utilized Job wizard to create job */