The SET_JOB_ARGUMENT_VALUE procedure sets a value for a job argument.
The SET_JOB_ARGUMENT_VALUE procedure overrides the default values that are set for the corresponding program or stored procedure arguments. You can set an argument by specifying its position or name. You can also call the DEFINE_PROGRAM_ARGUMENT procedure to name an argument.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
Set the argument value by position
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (
job_name IN VARCHAR2,
argument_position IN PLS_INTEGER,
argument_value IN VARCHAR2);
Set the argument value 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 be changed. |
| argument_name | The name of the program argument. |
| argument_position | The position of the program argument. |
| argument_value | The new value to be set for the program argument. |
Considerations
To call the SET_JOB_ARGUMENT_VALUE procedure, you must be the owner of the job or have the ALTER privilege on the job. You can also define a job argument if you have the CREATE ANY JOB privilege.
The SET_JOB_ARGUMENT_VALUE procedure supports only the arguments of the SQL type. Therefore, a program or job argument does not support a non-SQL value, such as a Boolean value.
You can call the SET_JOB_ARGUMENT_VALUE to set only VARCHAR2 arguments for lightweight jobs.
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;