Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 | SQL> exec dbms_scheduler.set_scheduler_attribute('email_server','192.168.1.10'); |
The smtp_out_server parameter should also be set as follows.
1 2 3 | SQL> alter system set smtp_out_server='192.168.1.10' scope=both sid='*'; System altered. |
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:
1 2 3 4 5 6 7 8 9 10 | BEGIN DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION ( job_name => 'JOB_NAME', subject => 'Job Notification-%job_owner%.%job_name%-%event_type%', body => '%event_type% occured on %event_timestamp%. %error_message%', events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED'); END; / |
Create notification for a single event:
1 2 3 4 5 6 7 | BEGIN DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION ( job_name => 'JOB_NAME', events => 'JOB_BROKEN'); END; / |
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.
1 2 3 4 5 6 7 8 9 10 11 | BEGIN DBMS_SCHEDULER.ADD_JOB_EMAIL_NOTIFICATION ( job_name => 'JOB_NAME', subject => 'Job Notification-%job_owner%.%job_name%-%event_type%', body => '%event_type% occured on %event_timestamp%. %error_message%', events => 'JOB_FAILED', filter_condition => ':event.error_code=600 or :event.error_code=700'); END; / |
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
———– ———————– ——————-
1 2 3 4 5 6 7 8 | TEST_USER jobnotification@deneme.com JOB_FAILED TEST_USER jobnotification@deneme.com JOB_BROKEN TEST_USER jobnotification@deneme.com JOB_SCH_LIM_REACHED TEST_USER jobnotification@deneme.com JOB_DISABLED TEST_USER jobnotification@test.com JOB_FAILED TEST_USER jobnotification@test.com JOB_BROKEN TEST_USER jobnotification@test.com JOB_SCH_LIM_REACHED TEST_USER jobnotification@test.com JOB_DISABLED |
Delete defined notifications:
We can use the DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION procedure for deletion.
1 2 3 4 5 6 7 | BEGIN DBMS_SCHEDULER.REMOVE_JOB_EMAIL_NOTIFICATION ( job_name => 'JOB_ISMI', events => 'JOB_FAILED, JOB_BROKEN, JOB_DISABLED, JOB_SCH_LIM_REACHED'); END; / |
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.
