Database Software

Dear all, I need help. I need few more uningaged pairs of eyes with curious brains behind it.
My problem is that I'm stuck finding the reason why my chain runs with no effect with two failed steps. It runs and finishes in seconds not doing what it ought to. I believe it is not important what exactly those two procedures do. It only worth mentioning that chain steps are procedure-based (each step is to call a pl/sql procedure) and that each procedure is valid and does the trick if run separately.

Here are the details:

1. Chain creation:

This part crates the empty chain. And marks it enabled.

BEGIN
    DBMS_SCHEDULER.create_chain(
        comments     => 'Job Chain by Rost to delete some recs in tbl PVJOURNAL and then - in tbl EVENT',
        chain_name   => 'Rosts_DEL_FROM_2_TBLS'
    );
    DBMS_SCHEDULER.enable(
        name   => 'Rosts_DEL_FROM_2_TBLS'
    );
END;

2. 1st step creation

This step defines the first step of the chain and alters some of it's attributes to make the step restartable. Every first call of  DBMS_SHEDULER.* subroutine is done in a named parameters manner in order to be ... more obvious. But further down the same calls are done in ... more code efficiant manner.

-- Incerting 1st step +setup of its attributes
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
        CHAIN_NAME     => 'Rosts_DEL_FROM_2_TBLS',
        STEP_NAME      => 'DEL_FROM_PVJOURNAL',
        PROGRAM_NAME   => 'PROVIEW.TMP_DEL_2_ITCM4052'
    );
      DBMS_SCHEDULER.ALTER_CHAIN(
          CHAIN_NAME   => 'Rosts_DEL_FROM_2_TBLS',
          STEP_NAME    => 'DEL_FROM_PVJOURNAL',
          ATTRIBUTE    => 'PAUSE',                --https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CHDGAEDD
          VALUE        => false
      );
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_PVJOURNAL', 'SKIP', false);
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_PVJOURNAL', 'RESTART_ON_FAILURE', TRUE);
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_PVJOURNAL', 'RESTART_ON_RECOVERY', TRUE);
END;

3. 2nd step creation

-- Incerting 2nd step and it's attributes.
BEGIN
    DBMS_SCHEDULER.DEFINE_CHAIN_STEP('Rosts_DEL_FROM_2_TBLS', 'Del_from_EVENT'
                                     ,'PROVIEW.TMP_DEL_LOG_ITCM4052');
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_EVENT', 'PAUSE', false);
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_EVENT', 'SKIP', false);
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_EVENT', 'RESTART_ON_FAILURE', TRUE);
      DBMS_SCHEDULER.ALTER_CHAIN('Rosts_DEL_FROM_2_TBLS', 'DEL_FROM_EVENT', 'RESTART_ON_RECOVERY', TRUE);
END;

4. Filling the chain with rules.

3 simple rules where 1st rule is to start the chain, 2nd rule is normal, conditional, rule and 3rd is final rule that finalizes the chain.

All rules are based on stored procedures. 1st rule is done in a named way so that it'd be more understandable, and other two are created im code-efficient manner. And 1st rule's run condition is hardcoded to true as it is first one and has to run always.

-- Rulez of steps' interdependencies are set herebelow
-- https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sched.htm#CHDBAFJE
BEGIN
  DBMS_SCHEDULER.define_chain_rule(
      chain_name   => 'Rosts_DEL_FROM_2_TBLS',
      condition    => 'TRUE',
      action       => 'START DEL_FROM_PVJOURNAL',
      rule_name    => 'Run_1st_step',
      comments     => 'First rule in the Rosts_DEL_FROM_2_TBLS chain. Simply runs 1st chain.'
  );
  DBMS_SCHEDULER.define_chain_rule
                   ('Rosts_DEL_FROM_2_TBLS'
                     ,'DEL_FROM_PVJOURNAL COMPLETED'
                     ,'START DEL_FROM_EVENT'
                     ,'Run_2nd_step'
                     ,'SECOND rule in the Rosts_DEL_FROM_2_TBLS chain. Runs when 1st step succeded.'
                    );
                   
  DBMS_SCHEDULER.define_chain_rule
                   ('Rosts_DEL_FROM_2_TBLS'
                     ,'DEL_FROM_EVENT COMPLETED '
                     ,'END'
                     ,'Ending_The_Chain'
                     ,'FINAL rule in the Rosts_DEL_FROM_2_TBLS chain. Runs when 2nd step succeded to end the chain.'
                    );                   
END;

5. Pre-launch check-up.

By now chain is filled with steps and select chain_name, number_of_rules, number_of_steps, enabled from all_scheduler_chains return this:

And select rule_name, rule_condition, rule_action_context, rule_comment from all_rules return presence of all trhee rules:

6. Run methods and results.

When I run the script herebelow to run the chain without main job creation - the script executes in a blink of an eye.

BEGIN
DBMS_SCHEDULER.RUN_CHAIN (
   chain_name    =>  'Rosts_DEL_FROM_2_TBLS',
   job_name      =>  'Rosts_Chained_JOB',
   start_steps   =>  null);
END;

But nothing is done and logs extracted with SELECT * FROM Dba_Scheduler_Job_Log where job_name like 'ROSTS%' and SUBSTR(log_date, 1, 10) = '26.06.2019' order by log_date produces this:

As I've stated previously, procedures that steps are to call, - they are valid and working. In fact, one of the precedures is working now, being run as a separate standalone job, started like this:

-- create a job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name          => 'Del_Events'
   ,job_type          =>  'PLSQL_BLOCK'
   ,job_action        =>  'BEGIN PROVIEW.TMP_DEL_2_ITCM4052; END;'
   ,comments         => 'My first job. It deletes from proview.PVJOURNAL using proview.tmp_del_log_ITCM4052 procedure.'
   ,enabled          => TRUE 
   ,auto_drop        => TRUE
   );
END;

P.S. All pl/sql pieces are real and actual and were run in SQl Navigator.

Any idea where have I go wrong in spinning the chain?

DBMS_SCHEDULER.