11-3. Using E-mail for Job Status Notification

Problem

You have a scheduled job that is running on a regular basis, and you need to know whether the job fails for any reason.

Solution

Use the ADD_JOB_EMAIL_NOTIFICATION procedure to set up an e-mail notification that sends an e-mail when the job fails to run successfully. Note, this solution builds on Recipe 11-1 where a nightly batch job was set up to calculate commissions.

How It Works

The previous recipe is the simplest example of automating e-mail in the event a job fails. The ADD_JOB_EMAIL_NOTIFICATION procedure accepts several parameters; however, the only required parameters are JOB_NAME and RECIPIENTS. The JOB_NAME must already exist from a previous call to the CREATE_JOB procedure (see Recipe 11-1 for an example). The RECIPIENTS is a comma-separated list of e-mail addresses to receive e-mail when an event occurs; by default the events that trigger an e-mail are JOB_FAILED, JOB_BROKEN, JOB_SCH_LIM_REACHED, JOB_CHAIN_STALLED, and JOB_OVER_MAX_DUR. Additional event parameters are job_all_events, job_completed, job_disabled, job_run_completed, job_started, job_stopped, and job_succeeded.

The full format of the ADD_JOB_EMAIL_NOTIFICATION procedure accepts additional parameters, but the default for each is sufficient to keep tabs on the running jobs. The body of the e-mail will return the error messages required to debug the issue that caused the job to fail.

To demonstrate the notification process, the commissions table was dropped after the job was set up to run. The database produced an e-mail with the following subject and body: