The SET_JOB_ARGUMENT_VALUE procedure is used to set the value of a job parameter.
SET_JOB_ARGUMENT_VALUE will override the default value set for the corresponding program or stored procedure. Parameters can be specified by position or by name. Alternatively, you can use the DEFINE_PROGRAM_ARGUMENT procedure to assign a name to a parameter.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
Set the value of a parameter by position
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN VARCHAR2);
Set the value of a parameter by name
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_name IN VARCHAR2,
argument_value IN VARCHAR2);
Parameters
| Parameter | Description |
|---|---|
| job_name | The name of the job to modify. |
| argument_name | The name of the program parameter being set. |
| argument_position | The position of the program parameter being set. |
| argument_value | The new value to set for the program parameter. |
Considerations
SET_JOB_ARGUMENT_VALUE requires that you are the owner of the job or have the ALTER privilege on the job. If you have the CREATE ANY JOB privilege, you can also set job parameter values.
SET_JOB_ARGUMENT_VALUE supports only SQL-type parameters. Therefore, program or job parameters do not support non-SQL-type parameter values, such as boolean values.
However, SET_JOB_ARGUMENT_VALUE can be used to set parameters for lightweight jobs only when the parameter type is VARCHAR2.
Examples
BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
program_name => 'empprog',
program_type => 'STORED_PROCEDURE',
program_action => 'hr.empproc',
number_of_arguments => 1,
enabled => FALSE);
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (
program_name =>'empprog',
argument_name =>'emp_no',
argument_position => 1,
argument_type =>'VARCHAR2',
default_value => '0',
out_argument => false);
DBMS_SCHEDULER.ENABLE (job_name => 'empprog');
DBMS_SCHEDULER.DROP_JOB (
job_name =>'job_insert_emp',
force => TRUE);
DBMS_SCHEDULER.CREATE_JOB (
job_name =>'job_insert_emp',
program_name =>'empprog',
auto_drop => TRUE,
start_date => SYSDATE,
job_style => 'LIGHTWEIGHT');
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name =>'job_insert_emp',
argument_name =>'emp_no',
argument_value =>'1357');
DBMS_SCHEDULER.ENABLE ( job_name => 'job_insert_emp');
END;