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

---清理之前的測試環境
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

dbms_scheduler package系列(六)

相關文章