Main | Reporting on latest run of Oracle Scheduled Jobs »
Tuesday
Jun152010

E-mail reports for Oracle Scheduled Jobs

  • Report on status of Oracle Scheduled Jobs (SUCCEEDED/FAILED)
  • Send e-mail alerts to stakeholders when Oracle jobs fail

Recently a number of business types were getting antsy when their numbers didn't match up.  Seems they'd prefer to know right off when the scheduled Oracle jobs meant to update their data don't work rather than spend hours tallying numbers, backtracking, and re-tallying only to find out the god-forsaken job didn't run.

I looked through the alert profile settings exposed via OEM (in Oracle 10g, mind you) and there didn't appear to be a good way to send off an alert when a scheduled job in Oracle had failed. 

I needed to generate an e-mail with the status of the job to the appropriate stakeholders, so they wouldn't waste time with their tallying and would simply know straight away if the job had failed.

First I enabled the UTL_MAIL procedure, created a global synonym for it, and granted permissions to it.  Google that.  Installation is simply a matter of running a couple of built-in scripts and knowing what your SMTP server is.

Then I tweaked a query for the most recent run of a job (ROWNUM <= 1) from the dba_scheduler_job_run_details table.

The whole thing gets wrapped into its own job, of the PL/SQL variety, which can run on any schedule to notify your stakeholders of the job status.

I've trimmed the query results to select rows even though I'm populating the variable with all columns (SELECT *), so you can see how to specify different values and figure out the %ROWTYPE usage.

I also left a comment so you can see how to leverage the built-in UTL_MAIL package's method to attach .txt files to your e-mail. 

 

 

 

Reader Comments

There are no comments for this journal entry. To create a new comment, use the form below.

PostPost a New Comment

Enter your information below to add a new comment.

My response is on my own website »
Author Email (optional):
Author URL (optional):
Post:
 
Some HTML allowed: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <code> <em> <i> <strike> <strong>