只有存在DBMS_SCHEDULER.DROP_JOB [英] DBMS_SCHEDULER.DROP_JOB only if exists

问题描述

我有一个导入转储后必须运行的sql脚本.该脚本除其他功能外，还执行以下操作:

I have a sql script that I must run after I import a dump. among other things the script does, it does the following:

BEGIN 
--remove program          
SYS.DBMS_SCHEDULER.DROP_PROGRAM(program_name=>'STATISTICS_COLUMNS_PROG',FORCE=>TRUE);
--remove job
SYS.DBMS_SCHEDULER.DROP_JOB (job_name => 'STATISTICS_COLUMNS_JOB');
END; 

有时作业已被放置在原始架构中，转储不包含作业，脚本失败:

Somtimes the job was already dropped in the original schema, the dump comes without the job and the script fails:

ERROR at line 1:
ORA-27475: "DMP_6633.STATISTICS_SET_COLUMNS_JOB" must be a job 
ORA-06512: at "SYS.DBMS_ISCHED", line 213 
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657 
ORA-06512: at line 5 

如果该作业不存在，但仍可以删除该作业，该如何避免该故障?

How can I avoid this failure in case the job does not exists but still be able to drop it if it is?

推荐答案

您可以将两种主要模式应用于异常处理. 在跳跃之前先看"(LBYL)和请求宽恕比允许容易"(EAFP). LBYL主张在尝试删除作业之前先检查该作业是否存在. EAFP将涉及尝试删除作业，然后捕获并忽略该特定错误(如果发生).

There are two main patterns you can apply to exception handling; "look before you leap" (LBYL) and "it's easier to ask forgiveness than permission" (EAFP). LBYL would advocate checking to see if the job exists before attempting to drop it. EAFP would involve attempting to drop the job and then capturing and ignoring that specific error, if it occurs.

如果要应用LBYL，则可以查询系统视图

If you were to apply LBYL you can query the system view USER_SCHEDULER_JOBS to see if your job exists. If it does, drop it.

declare
   l_job_exists number;
begin
   select count(*) into l_job_exists
     from user_scheduler_jobs
    where job_name = 'STATISTICS_COLUMNS_JOB'
          ;

   if l_job_exists = 1 then
      dbms_scheduler.drop_job(job_name => 'STATISTICS_COLUMNS_JOB');
   end if;
end;

对于EAFP，它略有不同；通过定义自己的异常. "http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS761" rel ="noreferrer">命名内部定义的异常，并使用错误代码实例化它正在寻找.如果随后出现该错误，则什么也不做.

For EAFP it's slightly different; define your own exception by naming an internally defined exception and instantiating it with the error code you're looking to catch. If that error is then raised, do nothing.

declare
   job_doesnt_exist EXCEPTION;
   PRAGMA EXCEPTION_INIT( job_doesnt_exist, -27475 );
begin
   dbms_scheduler.drop_job(job_name => 'STATISTICS_COLUMNS_JOB');
exception when job_doesnt_exist then
   null;
end;

关于第二种方法，需要注意两点.

It's worth noting two things about this second method.

1. 我只是 捕获此特定异常引发的错误.使用EXCEPTION WHEN OTHERS可以实现相同的目的，但是我强烈建议反对这样做.

1. I am only catching the error raised by this specific exception. It would be possible to achieve the same thing using EXCEPTION WHEN OTHERS but I would highly recommend against doing this.

如果您处理异常，则应该确切知道该怎么办.您不太可能具有使用OTHERS正确处理每个Oracle异常的能力，并且如果这样做，可能应该将它们记录在会被注意到的地方.引用Oracle的避免和处理异常的准则:

If you handle an exception you should know exactly what you're going to do with it. It's unlikely that you have the ability to handle every single Oracle exception properly using OTHERS and if you do so you should probably be logging them somewhere where they'll be noticed. To quote from Oracle's Guidelines for Avoiding and Handling Exceptions:

只要有可能，就为命名异常编写异常处理程序，而不要使用OTHERS异常处理程序.

Whenever possible, write exception handlers for named exceptions instead of using OTHERS exception handlers.

Oracle的异常传播从内部块到外部块工作，因此导致错误的最初原因将是第一个例外.

Oracle's exception propagation works from internal block to external block so the original cause for the error will be the first exception.

这篇关于只有存在DBMS_SCHEDULER.DROP_JOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！