CODEWITHSUNDEEP
oracle11gplsqldeveloper
Herman Plani Ginting
Herman Plani Ginting

Reputation: 61

How can I pass parameter needed by procedure that run through Scheduler Program

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

Upvotes: 1

Views: 85

Answers (2)

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

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;

Upvotes: 1

Herman Plani Ginting
Herman Plani Ginting

Reputation: 61

i changed it into program that run PLSQL 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 know how to keep it in previous format, will be appreciated.

Upvotes: 0

Related Questions