11g_job: Execute procedure with Parameters

11g_job executes the procedure [plain] with parameters of the date type. An existing storage parameter of the date type is: [SQL] create or replace procedure t_pro (t_var in date) as begin dbms_output.put_line (TO_CHAR (T_VAR, 'yyyy'); -- dbms_output.put_line (T_VAR); end;/the implementation code of the job is: [SQL] begin dbms_scheduler.create_job (job_name => 'ffffff ', job_type => 'stored _ PROCEDURE ', job_action => 't_pro', number_of_arguments => 1, repeat_interval => 'freq = DAILY; INTERVAL = 3', enabled => FALSE); end;/where job_type => 'stored _ PROCEDURE 'indicates that the job executes a stored procedure type, the parameter job_action => 'T _ Pro' indicates which stored procedure is executed (that is, the name of the stored procedure). The following is an explanation of this parameter in the official documentation: the action is the name of the stored procedure. you have to specify the schema if the procedure resides in another schema than the job. if case sensitivity is needed, enclose the schema name and the store procedure name in double quotes. for example, job_action_act Ion => '"Schema ". "Procedure "'. PL/SQL procedures with INOUT or OUT arguments are not supported as job_action when the job or program type is STORED_PROCEDURE. the user name is required in the official documentation. write the storage name like this! In addition, when there are input and output parameters in the stored procedure, job_type cannot be STORED_PROCEDURE. Of course, the job_type parameter is absent, and job_acton is not supported! Parameter number_of_arguments => 1 indicates the number of parameters of the execution program of the job (in this example, T_PRO) (the number of T_PRO is 1 ). This parameter must be set! This attribute specifies the number of arguments that the job expects. the range is 0-255, with the default being 0. parameter repeat_interval => 'freq = DAILY; INTERVAL = 3' indicates that the time for executing the job is every day! For how to set this parameter, see the official documentation parameter enabled => FALSE to indicate whether to enable the job. Set this parameter to FALSE. Note the case sensitivity! After the job is defined, you need to set the job parameter value: [SQL] begin DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name => 'ffffff', argument_position => 1, argument_value => SYSDATE ); end;/parameter argument_position => 1 indicates the location of the job parameter. If there are multiple parameters in the job, the value is 2, 3, 4 .... increments by 1. NOTE: If multiple parameters need to be specified, write [SQL] begin DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name => 'ffffff', argument_position => 1, argument_value => SYSDATE ); values (job_name => 'ffffff', argument_position => 2, argument_value => val2); DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE (job_name => 'ffff', argument_position => 3, argument_value => val3 );...... end;/parameter argument_value => SYS DATE indicates the definition of the value to be passed in. This parameter is explained on The official website as argument_value the new value to be set for The program argument. to set a non-VARCHAR value, use the SET_JOB_ANYDATA_VALUE procedure. set a new value for the parameter. To set a non-character value, use SET_JOB_ANYDATA_VALUE for storage !. SET_JOB_ARGUMENT_VALUE can be used to set arguments of lightweight jobs but only if the argument is of type VARCHAR2. set a value for the parameter stored in the lightweight job and the parameter type must be VARCHAR2! The official website clearly tells me that job parameter settings only support varchar2 !, However, the experiment proves that the date type parameter is supported !! (The verification process lasted for 2 hours) Enable job and execute job: [SQL] EXEC DBMS_SCHEDULER.ENABLE ('ffffff'); EXEC DBMS_SCHEDULER.RUN_JOB ('ffffff '); in fact, job parameters support date-type input parameters !! My machine VERSION is [SQL] SQL> select VERSION from v $ instance; VERSION --------------- 11.2.0.1.0