This discussion is archived
8 Replies Latest reply: Mar 8, 2013 11:24 AM by 978204 RSS

DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters

978204 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you. That answers my question.
  • 5. Re: DBMS_SCHEDULER.CREATE_PROGRAM with Optional input parameters
    978204 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points