3 Replies Latest reply on Jun 20, 2007 10:36 PM by Rnr-Oracle

    pass parameters to a program

    547817
      i belive every chain has a 'NOTIFY_DBA' program.
      i want to pass the error message as a parameter to the noty_dba program without having to declare different programs with default values attributes for each step.
      i wanted to know if thers any way i could pass parameters to a program on the fly.

      i have the folowing program :

      dbms_scheduler.create_program (
      program_name => 'NOTIFY_DBA'
      ,program_type => 'STORED_PROCEDURE'
      ,program_action => 'reports.notify_dba'
      ,number_of_arguments => 1
      ,comments => 'Send a message to the dba.');

      dbms_scheduler.define_program_argument (
      program_name => 'NOTIFY_DBA' ,
      argument_position => 1 ,
      argument_name => 'some_message' ,
      argument_type => 'VARCHAR2' ,
      default_value => 'Some message needs to be sent'
      );

      i want to be able to pass arguments to that program when defining the chain rules.
      something like that :

      dbms_scheduler.define_chain_rule (
      chain_name => 'reports.some_chain' ,
      condition => 'step_1 FAILED' ,
      action => 'START notify_dba(:step_1.error_message)',
      rule_name => 'rule_notify_some_error'
      );

      dbms_scheduler.define_chain_rule (
      chain_name => 'reports.some_chain' ,
      condition => 'step_2 FAILED' ,
      action => 'START notify_dba(:step_2.error_message)' ,
      rule_name => 'rule_notify_some_other_error'
      );
        • 1. Re: pass parameters to a program
          Rnr-Oracle
          Hi,

          There is no direct way of passing parameters to a chain step on the fly but there is a way to workaround it.

          You can have the program have metadata arguments which contain the job_owner, job_name and job_subname (normally the step_name). Once the program has the job info (including step name) it can query the jobrun_details table or you can have your own custom table which is keyed by job_name and contains data to be passed between steps.

          There is an example here
          Re: Manipulating Program Argument during Runtime

          Hope this helps,
          Ravi.
          • 2. Re: pass parameters to a program
            547817
            can i pass parameters other then those available with define_metadata ?
            (if not, then what's the use of define_program_argument others then assigning default values to program parameters ?)
            i thought of using some kind of context to pass parameters from on step to another. i would rather use a different way available by the scheduler.

            thanks !
            • 3. Re: pass parameters to a program
              Rnr-Oracle
              Hi,

              define_program_argument is mainly used for default program values as well as letting job definers know what argument values need to be filled in.

              But the only automatically filled in parameters available to the program are those available via define_metadata_argument.

              For steps in a chain, only program arguments with default values and metadata arguments can be used.

              However since metadata arguments include job_name, job_owner and job_subname these are sufficient to pass data between steps of a chain by using a table whose primary key is job_name and job_owner.

              Hope this helps,
              Ravi.