Oracle PL/SQL Best Practices

DBMS_ JOB

The DBMS_ JOB built-in package offers an API into an Oracle subsystem known as the job queue. The Oracle job queue allows for the scheduling and execution of PL/SQL routines (jobs) at predefined times and/or repeated job execution at regular intervals. DBMS_ JOB provides programs for submitting and executing jobs, changing job execution parameters, and removing or temporarily suspending job execution. And that’s all great, but DBMS_ JOB has several key weaknesses, including:

• Little or no job management features. A job is assigned an ID number, but you can’t give your job a name, which makes it hard to locate and manage the job after submission.

• Scheduling the frequency of execution can be complicated process. If you want a job to run every Monday, Wednesday, and Friday at noon, for example, you need to pass the following string to DBMS_ JOB.SUBMIT:

  'TRUNC(LEAST(NEXT_DAY,(SYSDATE, ''MONDAY''), 
               NEXT_DAY(,(SYSDATE, ''WEDNESDAY''), 
               NEXT_DAY(,(SYSDATE, ''FRIDAY''))) + 1/2'

As with the other built-in packages discussed in this chapter, you can overcome such weaknesses by building your own layer of code around the DBMS_ JOB procedures.