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:

  • job_all_events
  • job_broken
  • job_chain_stalled
  • job_completed
  • job_disabled
  • job_failed
  • job_over_max_dur
  • job_run_completed
  • job_sch_lim_reached
  • job_started
  • job_stopped
  • job_succeeded

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:

  • If the Events parameter is sent as NULL, all notifications defined for the specified recipients will be removed.
  • If the Recipients parameter is sent as NULL, then any notifications defined for the specified events will be removed.
  • If both Events and Recipients parameters are sent as NULL, all defined notifications will be removed.
  • If you try to remove a notification that was not previously defined during the remove notification process, no errors will be generated.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *