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.