Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a scheduler program that runs a stored procedure that requires input parameters.

BEGIN
  DBMS_SCHEDULER.DROP_PROGRAM
    (program_name          => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/
BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

How can I pass parameters to the EXPORT_STATUS procedure?

I changed it into program that runs a PL/SQL BLOCK so far:

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_PROGRAM
    (
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'PLSQL_BLOCK'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS(''STARTING''); END;'
     ,number_of_arguments  => 0
     ,enabled              => FALSE
     ,comments             => NULL
    );

  SYS.DBMS_SCHEDULER.ENABLE
    (name                  => 'MYSCHEMA.EXPORT_SCHEMA_STARTING');
END;
/

It works as I expected. But if anyone knows how to keep it in the previous format, will be appreciated.

share|improve this question
1  
I would recommend you to look at DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT and DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE procedures. –  Yasir Arsanukaev May 14 '13 at 3:47

1 Answer 1

When you create a program with CREATE_PROGRAM procedure, you can specify how many arguments it expects with number_of_arguments parameter.

If, for some reason, you want to modify some attribute (number_of_arguments in our case) for an existing Scheduler object (program in our case), you don't need to drop the object and re-create it with new attribute value again, you can just use SET_ATTRIBUTE procedure instead:

  dbms_scheduler.set_attribute(
      name => 'my_program'
    , attribute => 'number_of_arguments'
    , value => 1);

Once you've created the program and all of its attributes are set, you can define the arguments of the program (if there are any) with DEFINE_PROGRAM_ARGUMENT procedure:

dbms_scheduler.define_program_argument(
    program_name => 'my_program'
  , argument_position => 1
  , argument_type => 'VARCHAR2'
  , default_value => 'STARTING'
);

Here, we defined the program argument and its default value. We could have left the default value unspecified, but in this case we would have to set the value of the argument using SET_JOB_ARGUMENT_VALUE procedure. For example, if you created the job and specified the associated program as follows:

dbms_scheduler.create_job(
    job_name => 'call_my_program'
  , program_name => 'my_program'
  ...
);

You would have to set the value of the argument in the program associated with the job this way:

dbms_scheduler.set_job_argument_value(
    job_name => 'call_my_program'
  , argument_position => 1
  , argument_value => 'STARTING');

If you have defined the default value of an argument for a program with DEFINE_PROGRAM_ARGUMENT procedure and also set the value of the argument with SET_JOB_ARGUMENT_VALUE procedure, the argument value set with SET_JOB_ARGUMENT_VALUE procedure will override the default argument value set with DEFINE_PROGRAM_ARGUMENT procedure.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.