dbms_scheduler package系列(五)

最新推荐文章于 2022-07-21 15:24:13 发布

clg10051

最新推荐文章于 2022-07-21 15:24:13 发布

阅读量146

点赞数

继续测试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