dbms_scheduler package系列(六)

   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                                                                                     
 
 
小结:
     1,仔细看文档
     2,create_job有多个重载方法,选择自己适合的方法,如本例
     3,事先创建一个schedule(用create_schedule方法),然后运行2步所涉及的create_job重载方法
    
好,继续测试
2,job引用一个program对象,而你想用新设置的job arguments覆盖其默认值  

---清理之前的测试环境
SQL> begin
  2  dbms_scheduler.drop_job(job_name => 'job_argument',force=>true);
  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
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------

---创建一个program
--沿用之前创建的program
SQL> select * from user_scheduler_programs where program_name='PROGRAM_1';
 
PROGRAM_NAME                   PROGRAM_TYPE     PROGRAM_ACTION                                                                   NUMBER_OF_ARGUMENTS ENABLED DETACHED SCHEDULE_LIMIT                                                                    PRIORITY     WEIGHT   MAX_RUNS MAX_FAILURES MAX_RUN_DURATION                                                                NLS_ENV                                                                          COMMENTS
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PROGRAM_1                      STORED_PROCEDURE proc_arg                                                                                           0 TRUE    FALSE                                                                                             3          1                                                                                                         NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC program 1 comment

---未配置jog(program) argument之前   
SQL> select * from user_scheduler_program_args;
 
PROGRAM_NAME                   ARGUMENT_NAME                  ARGUMENT_POSITION ARGUMENT_TYPE                                                 METADATA_ATTRIBUTE  DEFAULT_VALUE                                                                    DEFAULT_ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- ------------------- -------------------------------------------------------------------------------- --------------------- ------------
    
----经查可用如下create_job的重载过程    
Creates a job using a named program object and an inlined schedule:
 DBMS_SCHEDULER.CREATE_JOB (
   job_name             IN VARCHAR2,
   program_name         IN VARCHAR2,
   start_date           IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   repeat_interval      IN VARCHAR2                 DEFAULT NULL,
   end_date             IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
   job_class            IN VARCHAR2                 DEFAULT 'DEFAULT_JOB_CLASS',
   enabled              IN BOOLEAN                  DEFAULT FALSE,
   auto_drop            IN BOOLEAN                  DEFAULT TRUE,
   comments             IN VARCHAR2                 DEFAULT NULL);
  
--开始创建job
SQL> begin
  2  dbms_scheduler.create_job(job_name => 'argument job',program_name=>'PROGRAM_1');
  3  end;
  4  /
 
begin
dbms_scheduler.create_job(job_name => 'argument job',program_name=>'PROGRAM_1');
end;
 
ORA-27452: argument job is an invalid name for a database object.--说明job name的字符要连接在一起,不能是a b,而应是a-b
ORA-06512: at "SYS.DBMS_ISCHED", line 124
ORA-06512: at "SYS.DBMS_SCHEDULER", line 419
ORA-06512: at line 3

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-754406/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-754406/