Email notification for failed dbms_scheduler jobs in Oracle

For any of the failed or broken dbms_scheduler job, we can always configure the job to schedule notification in case of any event.

The various events can be :

Event Type	Description
job_all_events	Not an event, but a constant that provides an easy way for you to enable all events
job_broken	The job has been disabled and has changed to the BROKEN state because it exceeded the number of failures defined by the max_failures job attribute
job_chain_stalled	A job running a chain was put into the CHAIN_STALLED state. A running chain becomes stalled if there are no steps running or scheduled to run and the chain evaluation_interval is set to NULL. No progress will be made in the chain unless there is manual intervention.
job_completed	The job completed because it reached its max_runs or end_date
job_disabled	The job was disabled by the Scheduler or by a call to SET_ATTRIBUTE
job_failed	The job failed, either by throwing an error or by abnormally terminating
job_over_max_dur	The job exceeded the maximum run duration specified by its max_run_duration attribute.
job_run_completed	A job run either failed, succeeded, or was stopped
job_sch_lim_reached	The job's schedule limit was reached. The job was not started because the delay in starting the job exceeded the value of the schedule_limit job attribute.
job_started	The job started
job_stopped	The job was stopped by a call to STOP_JOB
job_succeeded	The job completed successfully

     
   --
   -- Check the mailing parameters for the DB
   --

set serveroutput on size 100000
set lines 200 pages 10000
declare
  v_value varchar2(2000);
begin
  dbms_scheduler.get_scheduler_attribute('current_open_window',v_value);
  dbms_output.put_line('current open window '||v_value);
  dbms_scheduler.get_scheduler_attribute('default_timezone',v_value);
  dbms_output.put_line('default_timezone '||v_value);
  dbms_scheduler.get_scheduler_attribute('email_sender',v_value);
  dbms_output.put_line('email_sender '||v_value);
  dbms_scheduler.get_scheduler_attribute('email_server',v_value);
  dbms_output.put_line('email_server '||v_value);
  dbms_scheduler.get_scheduler_attribute('event_expiry_time',v_value);
  dbms_output.put_line('event_expiry_time '||v_value);
  dbms_scheduler.get_scheduler_attribute('log_history',v_value);
  dbms_output.put_line('log_history '||v_value);
  dbms_scheduler.get_scheduler_attribute('max_job_slave_processes',v_value);
  dbms_output.put_line('max_job_slave_processes '||v_value);
end;
/

-- Enable Email notification for the job events.

DECLARE

    l_job_name           VARCHAR2(100); 

BEGIN 

    -- Get a unique Job# 

    l_job_name   := 'ABV_DS_' || to_char(systimestamp, 'yymmddhh24missFF');

    DBMS_SCHEDULER.create_job (
        job_name            => l_job_name,
        job_type            => 'STORED_PROCEDURE',
        job_action          => 'ABV_SP', -- Procedure Name
        enabled             => FALSE);

    DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION (
        job_name   =>  l_job_name,
        recipients =>  'my_email@my_domain.com',
        sender     =>  'do_not_reply@my_domain.com',
        subject    =>  'SQS Job Broken-%job_owner%.%job_name%-%event_type%',
        body       =>  '%event_type% occurred at %event_timestamp%. %error_message%',
        events     =>  'JOB_BROKEN,JOB_FAILED');

    -- Enable Job   
    DBMS_SCHEDULER.ENABLE                 (
        name                =>  l_job_name); 

END;

/

References:
http://docs.oracle.com/cd/E18283_01/server.112/e17120/scheduse008.htm#CIAIEJEJ
http://www.dbspecialists.com/oracle-scheduled-job-email/