Oracle PL/SQL Best Practices

Name

BIP-11: Trap all errors in DBMS_ JOB-executed stored procedures and modify the job queue accordingly.

Synopsis

Oracle keeps track of the number of times a job fails (raises an unhandled exception); once it fails 16 times, the job is marked as “broken.” That’s handy, but not very practical. If a job breaks once, it will probably break again. More importantly, though, the DBA should be made aware of a job failure so that the code (or the execution environment) can be modified to allow the job to run successfully.

You can improve upon DBMS_ JOB by trapping all errors that occur in your job blocks and stored procedure calls. Immediately mark the job as broken, so that Oracle doesn’t try to run the job again and again, and send out an alert of some sort so that the job can be fixed.

Example

Now the myJob package will come in handy. With myJob, I can assign a name to a job and use that name to obtain the job ID number. This means that I can write an exception section in my stored procedure like this:

CREATE OR REPLACE PROCEDURE calculate_overdue_fines (...)
IS
   c_program CONSTANT VARCHAR2(30) := 
      'calculate_overdue_fines';
BEGIN
...
EXCEPTION
   WHEN OTHERS
   THEN
      myJob.broken (myJob.id (c_program);
      dba_beeper.notify (c_program, SQLERRM);
END;

Note that I don’t offer an implementation of the dba_beeper.notify procedure!

Benefits

Following this best practice will help you avoid repetitive, failed executions of jobs.

You can also repair broken jobs more quickly.

Challenges

Ensure that all developers ...