Oracle PL/SQL Best Practices

Name

BIP-10: Use your own submission procedure to improve job management capabilities.

Synopsis

As noted earlier, there’s no way to assign a name to a job with DBMS_ JOB. A job name comes in very handy for a number of purposes, including easy analysis of job status and a way to handle jobs that fail.

Rather than call DBMS_ JOB.SUBMIT directly, you should build an encapsulation around that procedure, which submits the job, but also keep track of additional job information.

Example

The myJob package (see Section ) offers a simple encapsulation of DBMS_ JOB submit that also populates a database table with additional job information (in this case, only the name of the job).

Here’s the submit procedure:

FUNCTION myJob.submit (
   name_in        IN   job.name%TYPE,
   what_in        IN   job.what%TYPE,
   next_date_in   IN   DATE := SYSDATE,
   interval_in    IN   job.interval%TYPE := NULL
)
   RETURN job.id%TYPE
IS
   retval   job.id%TYPE;
BEGIN
   DBMS_JOB.submit (retval, what_in, next_date_in, interval_in
   );

   INSERT INTO job
               (id, name, what, next_date, interval)
        VALUES (
           retval, name_in, what_in, next_date_in, interval_in
        );

   COMMIT;
   RETURN retval;
END submit;

Now that this information is available to me, you can use it in your other procedures. You can, for example, now remove a job by name:

BEGIN
   myJob.remove ('weekly_analysis');

Most important, you can reference the job by name within the exception section of the job’s stored procedure, which is crucial for managing jobs that fail (see [BIP-11: Trap all errors in DBMS_ JOB-executed stored ...