define an argument value for a step in an Oracle job chain

Question

I'm building a job chain in Oracle (11R2) DBMS Scheduler. The chain has two steps. Each step runs the same program, but with different arguments. I can see how how to define the chain, the steps, the rules, etc - but I cannot figure how to set the argument values for the steps.

When I build jobs that are single calls to programs, I set the arguments like this:

dbms_scheduler.set_job_argument_value(
   job_name    => 'MY_JOB',
   argument_position => 1,
   argument_value => 'foo');

My question is: Which dbms_scheduler func/proc would I call to set the arguments for a job step? Using the examples below, how would set an argument for 'STEP_1' in 'MY_CHAIN'?

Thanks, John

DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name => 'MY_CHAIN',
    rule_set_name => NULL,
    evaluation_interval => NULL,
    comments => 'Chain calls 2 steps. Same program but with different arg values.');

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name  => 'MY_CHAIN',
    step_name   => 'STEP_1',
    program_name => 'MY_PROGRAM');

DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name  => 'MY_CHAIN',
    step_name   => 'STEP_2',
    program_name => 'MY_PROGRAM');

DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'MY_CHAIN_JOB',
    job_type        => 'CHAIN',
    job_action      => 'MY_CHAIN',
    repeat_interval => 'freq=daily;byhour=12;byminute=0;bysecond=0',
    enabled         => TRUE);

AFter more searching it appears I can define arguments for steps in a chain. A workaround is to store them in a table and look up them from each step. I found this answer here: http://www.freelists.org/post/oracle-l/Passing-Arguments-to-Scheduler-Chains,1 — John, May 22 '12 at 22:08
Hi, can you post your final solution, I was having this problem too. The link John posted forwards to another post with oracle forum link but that page was deleted as it seems — Sherzodbek, Oct 11 '21 at 05:25

score 0 · Answer 1 · answered May 22 '12 at 22:07

I believe that you'd have to define two different programs for this, although they can of course reference the same stored procedure or executable.

In the case o the former unless I'm going to be modifying the argument values I tend to use anonymous block program types to specify the arguments to stored procedures -- it's complex enough without adding in all that program argument stuff.

define an argument value for a step in an Oracle job chain

1 Answers1