Site icon Database Tutorials

How To Add Email Notification for Scheduled Job

 

It is possible to define the email notification for the scheduled job. An e-mail can be sent when a job-related event occurs.

The following steps must be completed before creating a notification.

First of all, it is necessary to define “network acl” for the user who is running the job, and to give the necessary privileges.

Detailed information about this subject can be found in the article “What is Network Access Control List and How To Use It“.

Then, the e-mail server address must be set.

The smtp_out_server parameter should also be set as follows.

In addition, on the defined e-mail server, it is necessary to define the relay for the database servers.

After this process we can add notifications with DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION procedure.

Example:

We can specify parameters with ‘%’ in the procedure.

Create notifications for multiple events:

Create notification for a single event:

Create a notification based on a specific condition:

If the error code is 600 or 700 when the job is failed, it will send notification.

If the event is not specified in the procedure with the event parameter, a notification will occur for the following default events.

JOB_FAILED,

JOB_BROKEN,

JOB_SCH_LIM_REACHED,

JOB_CHAIN_STALLED,

JOB_OVER_MAX_DUR

A notification can be generated for the following events:

Individual events can be defined for these events, as well as a single notification for multiple events.

We can query defined notifications as follows:

SQL> SELECT JOB_NAME, RECIPIENT, EVENT FROM DBA_SCHEDULER_NOTIFICATIONS;

JOB_NAME RECIPIENT EVENT
———– ———————– ——————-

Delete defined notifications:

We can use the DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION procedure for deletion.

We can remove notifications in batch as above, as we can specify individual recipients and events to remove notifications.

The following conditions should be observed regarding the REMOVE_JOB_EMAIL_NOTIFICATION procedure:

Exit mobile version