dbms_scheduler包的使用及概念,已经编写了五篇文章.此文沿续测试dbms_scheduler package系列六;

SQL> begin
  2  dbms_scheduler.create_job(job_name => 'argument_job',job_type => 'stored_procedure',job_action => 'proc_arg',number_of_arguments => 2,enabled => true,comments => 'argument job comment');
  3  end;
  4  /
 
begin
dbms_scheduler.create_job(job_name => 'argument_job',job_type => 'stored_procedure',job_action => 'proc_arg',number_of_arguments => 2,enabled => true,comments => 'argument job comment');
end;
 
ORA-27457: argument 1 of job "SCOTT.ARGUMENT_JOB" has no value
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 271
ORA-06512: at line 3

--经查官方手册,错误原因如下:
ORA-27457: argument string of job "string.string" has no value
Cause: No value was provided for the job argument with the specified position.

---用set_job_argument_value配置参数值或者使用指定的program,为其参数指定一个默认值
Action: Provide a value for the job argument using any of the set_job_xxxx_value() routines.
Or, when using a named program, specify a default value for the corresponding argument of the program.  

---创建program
SQL> begin
  2  dbms_scheduler.create_program(program_name=>'program_1',program_type => 'STORED_PROCEDURE',program_action=>'proc_arg',enabled=>true,comments=>'program 1 comment');
  3  end;
  4  /
 
PL/SQL procedure successfully completed

---查看之前创建的scheduler
SQL> select * from user_scheduler_schedules;
 
SCHEDULE_NAME                  SCHEDULE_TYPE START_DATE                                                                       REPEAT_INTERVAL                                                                  EVENT_QUEUE_OWNER              EVENT_QUEUE_NAME               EVENT_QUEUE_AGENT              EVENT_CONDITION                                                                  FILE_WATCHER_OWNER                                                               FILE_WATCHER_NAME                                                                END_DATE                                                                         COMMENTS
------------------------------ ------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_SCHEDULE                    CALENDAR      31-JAN-13 11.03.49.798000 AM +08:00                                              FREQ=DAILY;BYHOUR=12,13                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           my_schedule_comment
 
---create_job有多个重载过程,经查如下实现带job argument的功能,但要先创建一个scheduler
Creates a job using a named schedule object and an inlined program:
 DBMS_SCHEDULER.CREATE_JOB (
   job_name                IN VARCHAR2,
   schedule_name           IN VARCHAR2,
   job_type                IN VARCHAR2,
   job_action              IN VARCHAR2,
   number_of_arguments     IN PLS_INTEGER       DEFAULT 0,
   job_class               IN VARCHAR2          DEFAULT 'DEFAULT_JOB_CLASS',
   enabled                 IN BOOLEAN           DEFAULT FALSE,
   auto_drop               IN BOOLEAN           DEFAULT TRUE,
   comments                IN VARCHAR2          DEFAULT NULL);
  
---创建引用已创建的schedule的且带有argument的job  
SQL> begin
  2  dbms_scheduler.create_job(job_name => 'job_argument',schedule_name=>'MY_SCHEDULE',job_type=>'STORED_PROCEDURE',job_action=>'proc_arg',number_of_arguments=>1,comments => 'job argument comments');
  3  end;
  4  /
 
PL/SQL procedure successfully completed  

--未设置job argument之前
SQL> select * from user_scheduler_job_args;
 
JOB_NAME                       ARGUMENT_NAME                  ARGUMENT_POSITION ARGUMENT_TYPE                                                 VALUE                                                                            ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------
 
SQL>

SQL> begin
  2  dbms_scheduler.set_job_argument_value(job_name => 'JOB_ARGUMENT',argument_position => 1,argument_value => '1');
  3  end;
  4  /
 
PL/SQL procedure successfully completed
 
SQL> select * from user_scheduler_job_args;
 
JOB_NAME                       ARGUMENT_NAME                  ARGUMENT_POSITION ARGUMENT_TYPE                                                 VALUE                                                                            ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------
JOB_ARGUMENT                                                                  1 UNDEFINED                                                     1