O'Reilly logo

Oracle PL/SQL Best Practices by Steven Feuerstein

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required