DBMS_SCHEDULER.DROP_JOB only if exists

Question

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?

Answer 1

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.

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;

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. 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.

   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:

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

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