6 Replies Latest reply on Jun 17, 2011 5:15 PM by Eric337-Oracle

    Passing Arguments to Scheduler Chains

    ora_et_labora
      Dear fellows of the Oracle,

      can I pass arguments to programs within a chain?

      What already works is calling a program from a job, passing an argument to the program. So far, so good.
      What I'm trying to do is to run a Scheduler Chain, where one of its steps starts the said program.

      Now, when I create a job to run the chain, I cannot add an argument value anymore with

      DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE

      because this throws an ora-27475.
      Looking at the docs it seems as if there's no way to do what I want - which I thought I could take for granted...
      Any ideas, anyone?

      I'm on 11.2.0.2.
      Kind regards,
      Uwe
        • 1. Re: Passing Arguments to Scheduler Chains
          mseberg
          There's a thread on here which might provide the answer you seek.

          Creating job, ran successfuly, but not seen on dba_scheduler_jobs

          ora-27475 is a questionable error, could this be from a user defined error?

          mseberg
          • 2. Re: Passing Arguments to Scheduler Chains
            56219
            "There is no direct way of passing parameters to a chain step on the fly but there is a way to workaround it." More details -> Re: pass parameters to a program
            • 3. Re: Passing Arguments to Scheduler Chains
              Eric337-Oracle
              This limitation is by design. You cannot pass user defined arguments to steps because the user is not calling the steps,
              just defining rules. The order of step execution is in general not deterministic.

              There are meta arguments passed to the step job, for example job_name and job_subname which are the master job
              name and the step name respectively. Another useful one is chain_id which is the log id of the master job run.
              This can be used as a key to a table the user would create to use as a global context for a job run to pass state.
              1 person found this helpful
              • 4. Re: Passing Arguments to Scheduler Chains
                ora_et_labora
                mseberg,

                not sure what you'd want me to read. This link has nothing about chains in it.
                Cheers,
                Uwe
                • 5. Re: Passing Arguments to Scheduler Chains
                  ora_et_labora
                  Eric,

                  thanks for the explanation about the (potentially) non-deterministic execution order. From this point of view I can understand the limitation, although I still find it quite uncomfortable to use the workaround that Eddie pointed me to.

                  On the other hand, if a step of my chain calls a program, maybe I just don't care about the execution order but I know that the arguments I need to pass to this program are the same at any point in time. That's exactly what happens when using the workaround with job metadata and a lookup table. From this point of view I cannot understand the limitation.
                  OK, there can be chain configurations where different steps could call the same program and then with different arguments -- but even that wouldn't be possible with this limitation in place. Now, with that woraround I'll have to care about another table, clean it up after job execution etc... not so good, don't you agree?

                  Enlighten me, please, should I have missed something.

                  Cheers,
                  Uwe
                  • 6. Re: Passing Arguments to Scheduler Chains
                    Eric337-Oracle
                    Yes, for constant arguments and other simple scenario's I can understand your frustration.

                    As for the housekeeping of SQL tables. You can easily modify a chain to create a new start step NEW_STEP
                    by adding a rule TRUE -> START new_start, modify all rules which start the old start to include AND NEW_START COMPLETED
                    and creates the table in the NEW_START program.
                    Then add a new end step NEW_END that cleans up the table and adding a rule "OLD_END COMPLETED->START NEW_END".
                    You may have to add some more steps/states if you have end rules with errors but you get the idea.

                    Edited by: Eric337 on Jun 17, 2011 10:10 AM
                    1 person found this helpful