How to send email from Oracle Database when an RDBMS_SCHEDULER Job fails
The journey began in 2008 where I officially started working in the field of IT (IT). Starting the first semester of school I realized a special attraction towards databases and automation. I have been involved with databases such as Microsoft SQL Server / Oracle Database, data analysis and automations using the command line (CLI), Visual Basic for Applications and Python. Through years of experience I have developed these capabilities so that I can make my life easier. For me, the purpose of every IT guy and every office worker is to have the knowledge so that through tools he can work a little but produce a lot. Through this his website DataPlatform.gr I try to offer knowledge and propose solutions to everyday problems.
Certifications:
Certifications:
Latest posts by Stratos Matzouranis (see all)
In this article we will see how we can install an email alert mechanism so that when a Job fails, the Oracle database sends us an email directly via SMTP Relay server. However, we will also see in general how we can send email via the procedure UTL_SMTP.
To send email from the RDBMS, we must grant access to the specific SMTP server IP using the Oracle Application Security access control lists (ACLs).
The installation
First we set the default SMTP relay server by entering its IP or host name:
ALTER SYSTEM SET smtp_out_server = '10.0.13.8' SCOPE=BOTH;
Configuring ACL (Oracle Application Security access control list) security
At this point we give the user we want the right to connect to the specific IP and port with a access control entry (ACE):
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => '10.0.13.8', -- SMTP server IP/hostname
lower_port => 25, -- Lower port (e.g., 25 for SMTP)
upper_port => 25, -- Upper port (same as lower for single port)
ace => xs$ace_type(
privilege_list => xs$name_list('connect'),
principal_name => 'SYS',-- Database user (e.g., 'SCOTT')
principal_type => xs_acl.ptype_db
)
);
END;
/
Let's see her ACE record we created for the specific server we run the following query:
SELECT HOST, LOWER_PORT, UPPER_PORT,
ACE_ORDER, PRINCIPAL, PRINCIPAL_TYPE,
GRANT_TYPE, INVERTED_PRINCIPAL, PRIVILEGE,
START_DATE, END_DATE
FROM (SELECT ACES.*,
DBMS_NETWORK_ACL_UTILITY.CONTAINS_HOST('10.0.22.8',
HOST) PRECEDENCE
FROM DBA_HOST_ACES ACES)
WHERE PRECEDENCE IS NOT NULL
ORDER BY PRECEDENCE DESC,
LOWER_PORT NULLS LAST,
UPPER_PORT NULLS LAST,
ACE_ORDER;
HOST LOWER_PORT UPPER_PORT ACE_ORDER PRINCIPAL PRINCIPAL_TYPE GRANT_TYPE INVERTED_PRINCIPAL PRIVILEGE START_DATE END_DATE -------------------------------------------------------------------------------- ---------- ---------- ---------- -------------------------------------------------------------------------------- -------------- ---------- ------------------ -------------------------------------------------------------------------------- ------------------------------------------------- ------------------------------------------------- 10.0.13.8 25 25 1 SYS DATABASE GRANT NO CONNECT
Alternatively we can create a new one ACL and give it the same permissions as before:
BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'smtp_server_acl.xml', -- Name of the ACL file
description => 'ACL for SMTP Server', -- Description
principal => 'SYS', -- Database user (e.g., 'SYS' or the job owner)
is_grant => TRUE, -- Grant access
privilege => 'connect', -- Required privilege
start_date => NULL, -- No start date restriction
end_date => NULL -- No end date restriction
);
END;
/
BEGIN
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'smtp_server_acl.xml', -- Name of the ACL file
host => '10.0.13.8',-- SMTP server hostname/IP
lower_port => 25, -- Lower port (e.g., 25 for SMTP)
upper_port => 25 -- Upper port (same as lower for single port)
);
END;
/
To see the ACLs where there are we run the following query:
SELECT * FROM dba_network_acls;
HOST LOWER_PORT UPPER_PORT ACL ACLID ACL_OWNER -------------------------------------------------------------------------------- ---------- ---------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- 10.0.13.8 25 25 /sys/acls/smtp_server_acl.xml 00000000800027D8 SYS
Test email using UTL_SMTP
Now to test that the SMTP server is working and that the security rules are set up correctly, we run the following to send a test email by setting the appropriate IP, sender, and recipient:
DECLARE
v_From VARCHAR2(80) := 'info@dataplatform.gr';
v_Recipient VARCHAR2(80) := 'info@dataplatform.gr';
v_Subject VARCHAR2(80) := 'Send mail test';
v_Mail_Host VARCHAR2(30) := '10.0.13.8';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13) || chr(10);
BEGIN
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, v_Recipient);
utl_smtp.Data(v_Mail_Conn,
'Date: ' || to_char(SYSDATE, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf || 'Subject: ' || v_Subject || crlf ||
'To: ' || v_Recipient || crlf || crlf ||
'some message text' || crlf || -- Message body
'more message text' || crlf);
utl_smtp.Quit(v_mail_conn);
END;
How to create an RDBMS_SCHEDULER Job that will send email alerting
We start by setting the default parameters for the IP/hostname of the SMTP relay server and the sender:
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.0.13.8:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'info@dataplatform.gr');
END;
/
To see the parameters we have set, we run the following query:
select * from dba_scheduler_global_attribute;
ATTRIBUTE_NAME VALUE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- MAX_JOB_SLAVE_PROCESSES LOG_HISTORY 30 DEFAULT_TIMEZONE Europe/Athens EMAIL_SERVER 10.0.13.8:25 EMAIL_SERVER_ENCRYPTION NONE EMAIL_SERVER_CREDENTIAL EMAIL_SENDER info@dataplatform.gr LAST_OBSERVED_EVENT EVENT_EXPIRY_TIME FILE_WATCHER_COUNT 0 CURRENT_OPEN_WINDOW
With the following procedure as an example, we create a job without being scheduled and add an email notification to a recipient when the event is JOB_FAILED:
BEGIN
sys.dbms_scheduler.create_job(job_name => 'TEST_MAIL_JOB',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN RAISE_APPLICATION_ERROR(-20001, 'DBMS_JOB Failed'); END;',
start_date => to_date('23-01-2025 00:00:00', 'dd-mm-yyyy hh24:mi:ss'),
end_date => to_date(null),
job_class => 'DEFAULT_JOB_CLASS',
enabled => true,
auto_drop => false,
comments => 'TEST_MAIL');
sys.dbms_scheduler.add_job_email_notification(job_name => 'TEST_MAIL_JOB',
sender => 'info@dataplatform.gr',
subject => '%JobFailed%',
body => '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%',
recipients => 'info@dataplatform.gr',
filter_condition => '',
events => 'JOB_FAILED');
sys.dbms_scheduler.set_attribute(name => 'TEST_MAIL_JOB', attribute => 'raise_events', value => sys.dbms_scheduler.job_failed);
END;
/
To see the dbms_scheduler job email notifications we have created, run the following query:
select * from all_scheduler_notifications WHERE job_name = 'TEST_MAIL_JOB';
OWNER JOB_NAME JOB_SUBNAME RECIPIENT SENDER SUBJECT BODY FILTER_CONDITION EVENT EVENT_FLAG
-------------------------------------------------------------------------------- ------------------------- -------------------------------------------------------------------------------- -------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- ------------------------------ ------------------- ----------
SYS TEST_MAIL_JOB info@dataplatform.gr info@dataplatform.gr %JobFailed% Job: %job_owner%.%job_name%.%job_subname% JOB_FAILED 512
So running the job as below should throw an error and send us the email:
BEGIN
DBMS_SCHEDULER.RUN_JOB('TEST_MAIL_JOB');
END;
/
To see the history of the status of the rdbms_scheduler job we created, run the following query:
SELECT job_name, status, log_date FROM DBA_SCHEDULER_JOB_LOG WHERE job_name = 'TEST_MAIL_JOB' ORDER BY log_date DESC;
JOB_NAME STATUS LOG_DATE ------------------------- ------------------------------ ------------------------------------------------- TEST_MAIL_JOB FAILED 29-JAN-25 01.27.48.007447 PM +02:00 TEST_MAIL_JOB FAILED 29-JAN-25 01.26.39.518793 PM +02:00
