4 Replies Latest reply: Jun 27, 2012 2:34 PM by Joe Upshaw RSS

    Proper Way to Exit Job Chain From Rule

    Joe Upshaw
      Basically, we have 2 ways that a chain rule may exit.

      One is on success which we specify like this
      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
           chain_name          => 'RISKDM2.EHS_REFRESH_CHAIN'
          ,condition           => '((L5_CONTRACTS COMPLETED AND L5_CONTRACTS SUCCEEDED) AND (L5_FUTURES_SETTLE COMPLETED AND L5_FUTURES_SETTLE SUCCEEDED) AND (L5_HUBS COMPLETED AND L5_HUBS SUCCEEDED) AND (L5_TRADER_MV COMPLETED AND L5_TRADER_MV SUCCEEDED))'
          ,action              => 'END '
          ,rule_name           => 'RISKDM2.CHAINEND'
          ,comments            => 'Chain End');
      Basically, the action is "END"

      When an error occurs, we abort the chain like this:
      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
           chain_name          => 'RISKDM2.EHS_REFRESH_CHAIN'
          ,condition           => '((L5_CONTRACTS COMPLETED AND L5_CONTRACTS NOT SUCCEEDED) OR (L5_FUTURES_SETTLE COMPLETED AND L5_FUTURES_SETTLE NOT SUCCEEDED) OR (L5_HUBS COMPLETED AND L5_HUBS NOT SUCCEEDED) OR (L5_TRADER_MV COMPLETED AND L5_TRADER_MV NOT SUCCEEDED))'
          ,action              => 'END 100'
          ,rule_name           => 'RISKDM2.ABBORT5'
          ,comments            => 'Abbort process - Job Chain Failure');
      We use the "END 100"

      The problem is that whenever we encounter an error. The job stops running as expected but, the chain itself, for some reason, is still in a running state. On the next run, the chain will stop (basically, the job will start and then immediately stop). After this, the job can be run normally.

      We are trying to figure out what action to specify when a chain step (or group of steps) fails so that the chain itself stops...and so that, once the condition has been corrected, the job will run normally on the very next run.

      Anyone know how to do this?

      Thanks,

      -Joe
        • 1. Re: Proper Way to Exit Job Chain From Rule
          Rnr-Oracle
          Hi,

          The end action is the right way to stop a chain.

          If a step of the chain throws an error the chain does not stop, you still need to have a rule which calls an END action.

          If you are calling the END action but your chain still requires another run to completely stop, you may be running into a known bug which has been fixed in later versions. If this is the case, oracle support should have access to a one-off fix for this issue for your version or you can update to a later version.

          Thanks,
          Ravi.
          • 2. Re: Proper Way to Exit Job Chain From Rule
            Joe Upshaw
            Ravi,

            Thanks for replying. As I said, I am trapping the failure and calling an "End Action" To be specific, the action is "END 100" - not sure where this came from or why the 100.

            Is that the "End Action" to which you refer?

            If so, then it does appear that I am hitting that bug. I don't suppose you have any reference for the bug # do you?

            Thanks,

            -Joe
            • 3. Re: Proper Way to Exit Job Chain From Rule
              Rnr-Oracle
              Hey,

              Yes that is the end action I am talking about. So it looks as if you are doing it right.

              Are you using run_job to run the job ?

              The bug I was referring to mostly occurs when run_job is used.

              It is tracked internally by bug #5705385. It is fixed in 10.2.0.5 and 11.1.0 . An official patch seems to be available for 10.2.0.3 .

              It does have the same symptom though

              " The job stops running as expected but, the chain itself, for some reason, is still in a running state. On the next run, the chain will stop (basically, the job will start and then immediately stop). After this, the job can be run normally."

              Thanks,
              Ravi.
              • 4. Re: Proper Way to Exit Job Chain From Rule
                Joe Upshaw
                Yep...that is exactly the set of symptoms. I will see if I can find the bug/patch on metalink. Thanks.