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. 

 

 

 

Wednesday
Dec292010

Reporting on latest run of Oracle Scheduled Jobs

Update:

I've found that the TIMESTAMP data type on the LOG_DATE column is a bit tedious to sort.  So here's a complete example query of how you'd get the latest run results for a specified job.  Note that I implemented a subquery to get the sort to work with intended results. 

As written you'll get the latest results (job_name, status, log_date, and run_duration columns) for the job "MY_job".

Cheers