Sending notification emails when the scheduled job fails in Oracle

Question

I have 4 scheduled jobs,if any of these job fails we have to send alert email regarding job failure to the concerned recipients.how can we do this in Oracle SQL Developer. Can we do this using DBMS_SCHEDULER? What is NOC Alert?

Answer 1

Yes, DBMS_SCHEDULER jobs can be configured to send an email after certain events occur. For example:

-- Configure scheduler emails --
BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mycompany.com');
END;
/

-- Create a job here --
...

-- Configure the job to send emails on failures: 
BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'test_notification_job',
  recipients =>  'tim@mycompany.com',
  events     =>  'job_failed');
END;
/

See ORACLE-BASE for more examples. And see the manual for an extremely thorough description of all the DBMS_SCHEDULER options.