3 Replies Latest reply on Apr 16, 2014 2:57 PM by Fritz-OC

    Chain configuration

    Fritz-OC

      Hi, all.

       

      Chain Newby here.

       

      I'm hoping someone can help orient me in the chain space.

       

      My problem ultimately is that my chain is stalling out and I'm not quite sure how to see why it stalls.

       

      In the syntax below **Please note**: load_dgr_cmpl_intrmdt_1 is a procedure within this schema.

       

      The syntax calls for a program name, but I am working on the assumption that a proc and program are interchangeable.  This may very well be my issue. I haven't been able to confirm or negate this assumption in my searches.  Any confirmation or resources shared along that assumption are very welcome (and appreciated).

       

      Would you please help confirm (or negate) my assumption and/or help with tips to troubleshoot this stalled chain?

       

      Thanks very much!

      Fritz

       

      -------------------------------------------------------------------------------------------Chain config syntax-----------------------------------------------------------------------------------------------

      I've configured the chain following Oracle's suggested commands (Shout out to Oracle! Thank you.):

       

      /* 1. Create Chain */

      BEGIN

      dbms_scheduler.create_chain (

      CHAIN_NAME => 'dgr_cmpl_chain_test',

      RULE_SET_NAME => null,

      EVALUATION_INTERVAL => null,

      comments => 'Testing');

      END;

       

       

      /* 2. Define the steps in the chain */

      BEGIN

      DBMS_SCHEDULER.DEFINE_CHAIN_STEP (

         chain_name      =>  'dgr_cmpl_chain_test',

         step_name       =>  'step_1',

         program_name    =>  'load_dgr_cmpl_intrmdt_1');

      END;

       

       

      /* 3. Add rules */

      BEGIN

      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (

         chain_name   =>   'dgr_cmpl_chain_test',

         condition    =>   'TRUE',

         action       =>   'START step_1',

         rule_name    =>   'test_rule_1',

         comments     =>   'Start Degree Completion chain.');

       

      DBMS_SCHEDULER.DEFINE_CHAIN_RULE (

         chain_name   =>   'dgr_cmpl_chain_test',

         condition    =>   'step_1 SUCCEEDED',

         action       =>   'END',

         rule_name    =>   'test_rule_2',

         comments     =>   'End Degree Completion chain.');

       

      END;

       

       

      /* 4. Enable the chain */

      BEGIN

      DBMS_SCHEDULER.ENABLE ('dgr_cmpl_chain_test');

      END;

       

       

      /* 5. Create a job that points to the chain. */

      /*Utilized Job wizard to create job */

      BEGIN

      DBMS_SCHEDULER.CREATE_JOB (

         job_name        => 'chain_job_test',

         job_type        => 'CHAIN',

         job_action      => 'dgr_cmpl_chain_test',

         repeat_interval => 'freq=hourly;byminute=20;bysecond=0',

         enabled         => TRUE);

      END;

        • 1. Re: Chain configuration
          GregV

          Hi Fritz,

           

          You do need to specify a program for the DEFINE_CHAIN_STEP procedure, not a stored procedure. See the documentation:

           

          http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN12458

           

          Defining Chain Steps

          After creating a chain object, you define one or more chain steps. Each step can point to one of the following:

          • A Scheduler program object (program)
          • Another chain (a nested chain)
          • An event schedule, inline event, or file watcher

           

          The named program or chain does not have to exist when defining the step. However it must exist and be enabled when the chain runs, otherwise an error is generated.

           

          "The named program or chain does not have to exist when defining the step". So that's probably the reason you don't get an error at creation, but you should get one when the chain runs.

          What does the ALL_SCHEDULER_RUNNING_CHAINS view show?

          1 person found this helpful
          • 2. Re: Chain configuration
            spajdy

            You have simple chain with one step. But chain ENDs only when step is completed successfuly. So change test_rule_2 condition    =>   'step_1 COMPLETED' or add new rule that END chain when step_1 failed.

            • 3. Re: Chain configuration
              Fritz-OC

              Thanks much for your suggestions.

               

              I've included both updates in my test and it seems to be working.

               

              So recapping the issues:

              1) was trying to use procedure instead of program.  (Indeed, when documentation refers to program, it means program.)

              2) the second suggest is helpful in that the chain will stop running in other events beside success.  I'll rely on my job (which kick starts the chain) to send failure, success notifications.

               

              Thanks again for contributing and helping out.

              Fritz