Send Email Notifications From DBMS_SCHEDULER Job

I recently converted CRONTAB jobs to DBMS_SCHEDULER. Partially motivated the fact I’m in the database more than I’m on the Linux box, and security of not having to use passwords and but mostly because it makes since. If I’m going to schedule a job that is going to access the database why not schedule it within the database. I have even done this for the exports.

I still want to be notified should the job fail, but could also receive notification when it starts and finishes.

Here are the steps I used:

1. Setup the necessary attributes such as the SMTP server and the email address to use as From:
Begin
dbms_scheduler.set_scheduler_attribute(’email_server’,’smtp_server:25);
dbms_scheduler.set_scheduler_attribute(’email_sender’,’me@wherever.com);
End;

2. Next create the job (if its not already created
Begin
dbms_scheduler.create_job(
job_name=> ‘Export_Job’,
job_type=> ‘PLSQL_BLOCK’,
job_action=>’BEGIN DATABASE_EXPORT; END;’,
start_date=> SYSTIMESTAMP,
repeat_interval => ‘Freq=daily; byminutely=0’
enable=>TRUE);
End;

3. Next associate the notification with the job just created (or already existing)
Begin
dbms_scheduler.add_job_email_notification (
job_ame=> ‘Export_Job’,
receiptants=> ‘notify@wherever.com’,
events=> ‘job_failed’);
End;

# Additionally I can add to events as such ‘job_started,job_succeeded,job_failed’ or any combination.
Just remember that the entire value must be enclosed in single ticks.

4. A filter condition can also be added so that on job_failed only certain oracle errors will trigger the
notification
Begin
dbms_scheduler.add_job_email_notification (
job_ame=> ‘Export_Job’,
receiptants=> ‘notify@wherever.com’,
events=> ‘job_failed’,
filter_condition=> ‘event.error_code=7445’);
End;

# the event.error_code can include whatever combination of oracle errors minus any leading zeros.

Notifications can be viewed via the dba_scheduler_notifications view.

To remove notifications:

dbms_scheulder.remove_job_email_notification

Tagged: dbms_scheduler