8 Replies Latest reply: Mar 8, 2013 1:24 PM by 978204 RSS

    DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters

    978204
      I have a procedure that has a number of "optional" parameters.

      procedure get_files(
      file_name_in in varchar2 default 'dummy_file',
      layout_in in number default 1,
      client_in in number default null,
      data_supplier_in in number default 99999
      );

      This procedure can be called with any combination of the input parameters.

      I can set up program(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'PLSQL_BLOCK' like this:

      begin
      sys.dbms_scheduler.create_program(
      program_name => 'GET_MY_FILES',
      program_action => '
      declare
      begin
      get_files( layout_in => 11111, client_in => 2222 );
      end ;',
      program_type => 'PLSQL_BLOCK',
      number_of_arguments => 0);
      end;


      My question is: Can I set up programs(s) using the DBMS_SCHEDULER.CREATE_PROGRAM procedure using a program_type => 'STORED_PROCEDURE' when I have "optional" parameters?

      It appears that ALL of the program input parameters must be defined and there is no way to indicate that a parameters is "optional".
        • 1. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
          spajdy
          Yes you can do it.
          You can defined default values for program parameters, but this values are "constants". So if your default value of parameter is defined by function call you have a problem, because you have to specify this parametr every time.
          ...
          procedure my_proc(p_date IN DATE := SYSDATE) AS
          ...
          There is my example, it using DBADMIN schema create this schema or replace it.
          create or replace procedure dbadmin.sleep(p_interval in number)
          as
          begin
            dbms_lock.sleep(p_interval);
          end;
          /  
          --program definition
          begin
              dbms_scheduler.create_program(program_name => 'dbadmin.prg_test1',
                                            program_type => 'STORED_PROCEDURE',
                                            program_action => 'dbadmin.sleep',
                                            number_of_arguments => 1);
              dbms_scheduler.define_program_argument(program_name => 'dbadmin.prg_test1',argument_position => 1,argument_type => 'NUMBER',default_value => 60);
              dbms_scheduler.enable(name => 'dbadmin.prg_test1');                                  
          end;
          /
          --check that program was created
          select * from dba_scheduler_programs
          where owner='DBADMIN';
          select * from dba_scheduler_program_args
          where owner='DBADMIN';
          
          BEGIN
              dbms_scheduler.create_job(job_name        => 'dbadmin.job_program1',
                                        program_name    => 'dbadmin.prg_test1',
                                        start_date      => to_timestamp_tz('1.1.2010 12:00 Europe/Prague', 'dd.mm.yyyy hh24:mi tzr'),
                                        repeat_interval => 'FREQ=minutely; INTERVAL=2;BYSECOND=0',
                                        auto_drop       => FALSE);
              dbms_scheduler.enable(name => 'dbadmin.job_program1');
          END;
          /
          
          --job run checks
          SELECT *
          FROM   dba_scheduler_jobs j
          WHERE  j.JOB_NAME = 'JOB_PROGRAM1';
          SELECT *
          FROM   dba_scheduler_job_log jl
          WHERE  jl.JOB_NAME = 'JOB_PROGRAM1';
          SELECT *
          FROM   dba_scheduler_job_run_details jr
          WHERE  jr.JOB_NAME = 'JOB_PROGRAM1';    
          
          --now set job parametr
          BEGIN
              dbms_scheduler.set_job_argument_value(job_name => 'dbadmin.job_program1', argument_position => 1, argument_value => 30);
          END;
          /
          --clean up
          BEGIN
              dbms_scheduler.drop_job(job_name => 'dbadmin.job_program1', force => TRUE);
              dbms_scheduler.drop_program(program_name => 'dbadmin.prg_test1');
              dbms_scheduler.purge_log;
          END;
          /
          • 2. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
            978204
            Thank you for the response, but I think you are missing the point of my question.

            My PROCEDURE has 4 input parameters:

            file_name_in in varchar2 default 'dummy_file',
            layout_in in number default 1,
            client_in in number default null,
            data_supplier_in in number default 99999

            Three of the input parameters have defaults which are set by the PROCEDURE.

            When setting up PROGRAMs that use this procedure, I could include default program parameters that "mimic" these same PROCEDURE defaults - and that would work until the defaults in the PROCEDURE were modified. I would then have to go back and change ALL of the PROGRAMs using this procedure, resetting all of default program parameters.

            So my question is, is there a way, using the DBMS_SCHEDULER.CREATE_PROGRAM procedure to NOT override the PROCEDURE defaults??
            Is there a way to NOT include an input parameter, therefore forcing it to use the PROCEDURE default value?
            • 3. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
              spajdy
              You precedure have 4 parameters with default values not 3.

              You simply can creater program that use this procedure and define that this program have less then 4 parameters but in this case only paratemers at the end of procedure parametr list use default values.
              It is same when you create a wrapper for you procedure with less parameters.
              begin
                  dbms_scheduler.create_program(program_name => 'schema.program_name',
                                                program_type => 'STORED_PROCEDURE',
                                                program_action => 'schema.my_procedure',
                                                number_of_arguments => 1);
                  dbms_scheduler.define_program_argument(program_name => 'schema.program_name',argument_position => 1,argument_type => 'VARCHAR2');
                  dbms_scheduler.enable(name => 'schema.program_name'');                                  
              end;
              /
              In this example you can set only file_name_in parameter for my_procedure. Others parameters are not set so default values are used.
              But you can't create program to use default for file_name and require value for parameter layout_in.

              But you can create wrapper procedure
              create or replace procedure my_wrapper(layout_in in number)
              as
              begin
                my_procedure(layout_in => layout_in);
              end;
              /
              When you create a program that use procedure my_wrapper then you in real use my_procedure with default values for parameters file_name_in,client_in,data_supplier_in and require value for parameter layout_in.

              You of course can define program with 4 parameters and define them default values. In this case it is true that when you change default parameters in my_procedure that is not automatically reflected in default values for parameters for program.
              You have to change it manually.
              • 4. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
                978204
                Thank you. That answers my question.
                • 5. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
                  978204
                  Perhaps in a future release of DBMS_SCHEDULER we will be able to use NAMED PROCEDURE parameters as well as positional parameters.
                  • 6. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
                    ChakravarthyDBA
                    Hi

                    How to pass the parameters to Schedule job of a Stored procedure from table and the schedule time also need to pass from table
                    • 7. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
                      978204
                      ChakravarthyDBA - you should post this as a NEW question as it has nothing to do with this question -and- this question has already been answered.
                      • 8. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
                        978204
                        ChakravarthyDBA - I have a potential answer for you. As soon as I see your NEW question posted in the SCHEDULER forum, I will post a rely.