繼續測試dbms_scheduler package系列五

---未配置job arguments之前的資訊
SQL> select * from user_scheduler_job_args;

JOB_NAME                       ARGUMENT_NAME                  ARGUMENT_POSITION ARGUMENT_TYPE                                                 VALUE                                                                            ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------

三種情形要配置job arguments:
   1,內聯job action是一個儲存過程或外部executable,它們需要引數
   2,job引用一個program物件,而你想用新設定的job arguments覆蓋其預設值
   3,job引用一個program object,program object未配置預設值

---測試上述幾種情形
---建立表
SQL> create table t_arg(a int);

Table created

SQL> insert into t_arg values(1);

1 row inserted

SQL> insert into t_arg values(2);

1 row inserted

SQL> commit;

Commit complete

SQL> select * from t_arg;

                                      A
---------------------------------------
                                      1
                                      2

--建立引用上述表且有輸入引數的儲存過程
SQL> create or replace procedure proc_arg(in_a pls_integer)
2 as
3 v_cnt pls_integer;
4 begin
5 select count(a) into v_cnt from t_arg where a=in_a;
6 end;
7 /

Procedure created

--驗證執行可行性
SQL> exec proc_arg(1);

PL/SQL procedure successfully completed

----建立與儲存過程相關的job
SQL> begin
2 dbms_scheduler.create_job(job_name => 'argument_job',job_type => 'stored_procedure',
3 job_action=>'proc_arg',start_date => sysdate,repeat_interval => 'freq=daily;interval=1',end_date=>sysdate+2,comments=>'argument job comments');
4 end;
5 /

PL/SQL procedure successfully completed

---構建具有輸入引數的儲存過程,再次查詢下述字典仍無引數值,怪怪?
SQL> select * from user_scheduler_job_args where job_name='ARGUMENT_JOB';

JOB_NAME ARGUMENT_NAME ARGUMENT_POSITION ARGUMENT_TYPE VALUE ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------

SQL> select * from DBA_scheduler_job_args where job_name='ARGUMENT_JOB';

OWNER JOB_NAME ARGUMENT_NAME ARGUMENT_POSITION ARGUMENT_TYPE VALUE ANYDATA_VALUE OUT_ARGUMENT
------------------------------ ------------------------------ ------------------------------ ----------------- ------------------------------------------------------------- -------------------------------------------------------------------------------- ------------- ------------

SQL>

----強制配置job argument 報錯
begin
dbms_scheduler.set_job_argument_value(job_name => 'ARGUMENT_JOB',argument_position => 1,argument_value => 1);
end;

ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION
ORA-06512: at "SYS.DBMS_ISCHED", line 244
ORA-06512: at "SYS.DBMS_SCHEDULER", line 698
ORA-06512: at line 3

SQL> ed
SQL>
SQL>
SQL>
SQL> begin
2 dbms_scheduler.set_job_argument_value(job_name => 'ARGUMENT_JOB',argument_position => 1,argument_value => '1');
3 end;
4 /

begin
dbms_scheduler.set_job_argument_value(job_name => 'ARGUMENT_JOB',argument_position => 1,argument_value => '1');
end;

ORA-27465: invalid value 1 for attribute ARGUMENT_POSITION
ORA-06512: at "SYS.DBMS_ISCHED", line 244
ORA-06512: at "SYS.DBMS_SCHEDULER", line 698
ORA-06512: at line 3

---經分析排錯,dbms_scheduler.set_job_argument_value僅能更新job referenced program,而上述job未與program關聯起來

---特附過程語義

Table 93-60 SET_JOB_ARGUMENT_VALUE Procedure Parameters

job_name
The name of the job to be altered

argument_name
The name of the program argument being set ---注:是program的argument

argument_position
The position of the program argument being set

argument_value
The new value to be set for the program argument. To set a non-VARCHAR value, use the SET_JOB_ANYDATA_ARGUMENT_VALUE procedure.

---與program相關的字典
SQL> desc user_scheduler_programs;
Name                Type                         Nullable Default Comments
------------------- ---------------------------- -------- ------- ------------------------------------------------------
PROGRAM_NAME        VARCHAR2(30)                                  Name of the scheduler program
PROGRAM_TYPE        VARCHAR2(16)                 Y                Type of program action
PROGRAM_ACTION      VARCHAR2(4000)               Y                String specifying the program action
NUMBER_OF_ARGUMENTS NUMBER                       Y                Number of arguments accepted by the program
ENABLED             VARCHAR2(5)                  Y                Whether the program is enabled
DETACHED            VARCHAR2(5)                  Y                This column is for internal use
SCHEDULE_LIMIT      INTERVAL DAY(3) TO SECOND(0) Y                Maximum delay in running program after scheduled start
PRIORITY            NUMBER                       Y                Priority of program
WEIGHT              NUMBER                       Y                Weight of program
MAX_RUNS            NUMBER                       Y                Maximum number of runs of program
MAX_FAILURES        NUMBER                       Y                Maximum number of failures of program
MAX_RUN_DURATION    INTERVAL DAY(3) TO SECOND(0) Y                Maximum run duration of program
NLS_ENV             VARCHAR2(4000)               Y                NLS Environment in which program was created
COMMENTS            VARCHAR2(240)                Y                Comments on the program

---檢視當前使用者的program
SQL> select * from user_scheduler_programs;

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
------------------------------ ---------------- -------------------------------------------------------------------------------- ------------------- ------- -------- ------------------------------------------------------------------------------- ---------- ---------- ---------- ------------ ------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
MY_EXP_JOB                     EXECUTABLE       c:\auto_exp.bat                                                                                    0 FALSE   FALSE                                                                                             3          1                                                                                                         NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENC my_exp_job_comment

dbms_scheduler package系列(五)

相關文章