Passing parameters to a procedure executed by DBMS_SCHEDULER

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.

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.

StackExchange Database Administrators Q#42119, answer score: 6