How do I pass the parameter required by the procedure that runs in the Scheduler program

advertisements

I have a scheduler program that run a store procedure that requires input parameter.

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;
/

i wonder how can i pass the parameter into EXPORT_STATUS procedure


You should be able to pass parameters using DEFINE_PROGRAM_ARGUMENT

BEGIN

  DBMS_SCHEDULER.CREATE_PROGRAM(
      program_name         => 'MYSCHEMA.EXPORT_SCHEMA_STARTING'
     ,program_type         => 'STORED_PROCEDURE'
     ,program_action       => 'MYSCHEMA.EXPORT_STATUS'
     ,number_of_arguments  => 1
     ,enabled              => FALSE
     ,comments             => NULL
    );

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
   program_name            => 'MYSCHEMA.EXPORT_SCHEMA_STARTING',
   argument_position       => 1,
   argument_name           => 'STATUS',
   argument_type           => 'VARCHAR2',
   default_value           => 'STARTING');

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