Dear Users,

This is very useful information to automate the email processes.

Scheduler has long offered various advantages over the older DBMS_JOB functionality. Now you have one more reason to like it.
When a job completes, how do you know? Querying the DBA_SCHEDULER_JOBS view repeatedly will tell you, but that’s hardly practical. A more practical solution is via an email. But that brings its own set of problems – from change control approval to not being able to modify the source code.

In Oracle Database 11g Release 2, you don’t have to resort to these options; there is a much more elegant alternative whereby the Scheduler can send an email after completion. It can even specify in the email whether the completion ended in success or failure.

To demonstrate, let’s create a job to run a stored procedure named process_etl2:

begin
  dbms_scheduler.create_job (
    job_name        => 'process_etl2',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'process_etl2',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; bysecond=0',
    enabled         => TRUE);
end;
/

Now, to enable the email send function, we have to set some email-related parameters

such as the email server name and how the sender’s details should be specified.

BEGIN  
  dbms_scheduler.set_scheduler_attribute(
    'email_server', 
    'mail.proligence.com:25'
  );  
  dbms_scheduler.set_scheduler_attribute(
     'email_sender', 
     'dbmonitor@proligence.com'
  );
END;
/

Note the SMTP server is given in the format server[:port]. If the port is not given, default 25 is assumed.

Now we can add the email notification property to the job:

begin
 dbms_scheduler.add_job_email_notification (
  job_name   =>  'process_etl2',
  recipients =>  'arup@proligence.com',
  subject    =>  'Job: process_etl',
  body       =>  'ETL Job Status',
  events     =>  'job_started, job_succeeded');
END;
/

The parameters of the procedure are self-explanatory. One very important one is EVENTS,

which specifies the events during which the emails should be sent. In this example we have specified that the email is sent when the job starts and when it succeeds (but not when it fails).

The EVENTS parameter can have table values job_failed, job_broken, job_sch_lim_reached, job_chain_stalled, job_over_max_dur, which represent if a job failed at the end, if a job was broken, if its limit in the scheduler was reached, if a chain which this job belongs to got stalled and if the job went over its duration, respectively.

If you want to find out the status of the email notification sent by the various jobs placed under this notification system, you can check the new data dictionary view USER_SCHEDULER_NOTIFICATIONS.

SQL> desc user_scheduler_notifications
 Name                                      Null?    Type
 -----------------------------------------                     --------     ----------------------------
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 RECIPIENT                                 NOT NULL VARCHAR2(4000)
 SENDER                                             VARCHAR2(4000)
 SUBJECT                                            VARCHAR2(4000)
 BODY                                               VARCHAR2(4000)
 FILTER_CONDITION                                   VARCHAR2(4000)
 EVENT                                              VARCHAR2(19)
 EVENT_FLAG                                NOT NULL NUMBER

Let’s check the contents of this view.

SQL> select job_name, recipient, event
  2  from user_scheduler_notifications;
JOB_NAME                  RECIPIENT            EVENT
-------------------------             --------------------           -------------------
PROCESS_ETL2              arup@proligence.com  JOB_STARTED
PROCESS_ETL2              arup@proligence.com  JOB_SUCCEEDED

The body column shows the actual mail that was sent:

SQL> select BODY, event_flag
  2  from user_scheduler_notifications
  3  /
BODY
--------------------------------------------------------------------------------
EVENT_FLAG
----------
ETL Job Status
         1
ETL Job Status
         2

The emails will keep coming based on the error defined.

You can also define a filter that results in the notification system sending out emails only if the error codes are ORA-600 or ORA-7445. The expression should be a valid SQL predicate (the WHERE clause without the “where” keyword).

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>  'process_etl2',
  recipients =>  'arup@proligence.com',
  subject    =>  'Job: process_etl',
  body       =>  'ETL Job Status',
  filter_condition =>  ':event.error_code = 600 or :event.error_code = 7445',
  events     =>  'job_started, job_succeeded');
END;
/

Did you notice that the body was a simple “ETL Job Status”? This is not quite useful.

It may be worth letting the default value take over for that. The email notifications are sent with the following body by default

Job: %job_owner%.%job_name%.%job_subname%
Event: %event_type%
Date: %event_timestamp%
Log id: %log_id%
Job class: %job_class_name%
Run count: %run_count%
Failure count: %failure_count%
Retry count: %retry_count%
Error code: %error_code%
Error message: %error_message%' 

As you can see the body has a lot of variables such as %job_owner%.

These variables are explained here:

Variable

Description

%job_owner% 

The owner of the job

%job_name% 

The name of the job

%job_subname% 

When the job is an event based one, this column shows the chain of the events

%event_type% 

The event that triggered the notification, e.g. job_broken, job_started, etc.

%event_timestamp% 

The time the event occurred

%log_id% 

The details of the job execution are located in the views DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_JOB_RUN_DETAILS. The column LOG_ID on those views corresponds to this column.

%error_code% 

The error code, if any

%error_message% 

The error message, if any

%run_count% 

The number of times this job has run

%failure_count% 

The number of times this job has failed

%retry_count% 

The number of times it has been retried after failure

To remove the notification, you can use another procedure in the same package:

begin
 dbms_scheduler.remove_job_email_notification (
  job_name   =>  'process_etl2');
end;
/

Email notification makes the job system complete. Remember, emails are triggered by the Scheduler Job system, not by the code inside the programs or procedures called by the Scheduler. This allows you to set the notification schedule independent of the actual code under the job.

Thanks to Oracle Technet