stored-proceduresplsqloracle10gnamed-parametersdbms-scheduler

Set named parameter for Oracle dbms_scheduler job stored procedure


Is it possible to passed named arguments to for dbms_scheduler job with a type 'stored_procedure' ? I've tried this way:

-- 1) example dummy procdure
CREATE OR REPLACE PROCEDURE my_test_proc (
 param1 IN NVARCHAR2,
 param2 IN NUMBER,
 param3 IN NUMBER
) IS
BEGIN
-- ...
END;

-- 2)Example dummy job:
BEGIN    
    dbms_scheduler.create_job(
        job_name => 'my_test_job'
        ,job_type => 'STORED_PROCEDURE'
        ,job_action => 'my_test_proc'
        ,start_date => sysdate
        ,number_of_arguments => 3
        ,enabled => FALSE
        ,auto_drop =>FALSE
    );
END;
-- 3)Set named param value:
BEGIN  
    dbms_scheduler.set_job_argument_value(
        job_name => 'my_test_job'
        ,argument_name => 'param1' 
        ,argument_value => 'some value'
    );
END;  

I get following error: ORA

-27484: Argument names are not supported for jobs without a program. ORA-06512: at "SYS.DBMS_ISCHED", line 207 ORA-06512: at "SYS.DBMS_SCHEDULER", line 602 ORA-06512: at line 2

I successfully set parameter values with set_job_argument_value by using argument_position setting. But there can be cases when I will need to run stored procedures to whom I will need to set only certain parameters and this could not work. Is there a way to pass named argument to stored procedure runed by scheduler job?


Solution

  • as the error states, create a program first, then the job on that.

    dbms_scheduler.create_program(program_name        => 'YOUR_PROGRAM',
                                  program_type        => 'STORED_PROCEDURE',                                                          
                                  program_action      => 'my_test_proc', 
                                  number_of_arguments => 2,
                                  enabled             => false,
                                  comments            => 'Comments you want');
    
    dbms_scheduler.define_program_argument(program_name      => 'YOUR_PROGRAM',
                                           argument_name     => 'param1',
                                           argument_position => 1,
                                           argument_type     => 'VARCHAR2',
                                           default_value     => '');
        ..etc, do for all 3.                                         
    
    dbms_scheduler.enable (name => 'YOUR_PROGRAM');
    
    
    dbms_scheduler.create_job(job_name        => 'my_test_job',
                              program_name    => 'YOUR_PROGRAM',
                              start_date      => systimestamp,
                              end_date        => null,
                              ...
    
    dbms_scheduler.set_job_argument_value(job_name          => 'my_test_job',
                                          argument_position => 1,
                                          argument_value    => 'value');
      ...