Send Email from OCI Email Delivery Service Through UTL_SMTP
Contents
Recently, we had a requirement to implement email notifications from services running in OCI. The customer requested email notifications from the DBCS and Operating systems (RHEL 7.9). In this article, I will explain how we achieved it.
OCI Email Delivery Service
OCI’s Email Delivery Service is a cloud-based, reliable, scalable email-sending service provided by Oracle Cloud Infrastructure (OCI). It is designed to allow applications and users to send large volumes of transactional emails to customers or recipients over the Internet. The service is primarily used for sending notifications, alerts, or transactional emails (such as order confirmations, password resets, or promotional messages). It integrates with various services, such as Oracle databases, and other Oracle cloud services, allowing seamless and secure email sending.
To send an email from an Oracle Database System (DB System) on OCI using the UTL_SMTP package, you need to set up and configure your database to use OCI’s Email Delivery Service.
Below are the steps to achieve this, including enabling SSL for secure communication.
- Set Up the IAM Policy
A user must be assigned to a group with permission to manage approved senders
Allow group <group name> to use approved senders in compartment <compartment name>
- Generate SMTP credentials for a User
SMTP credentials are necessary to send emails through Email Delivery. Each user is limited to a maximum of two SMTP credentials.
- Open the navigation menu. Go to Identity and click Domains. Click on the current domain. Under the Users, locate the user in the list that has permission to manage email, and then click the user's name to view the details.
- Under the Resources, click SMTP Credentials.
- Click Generate SMTP Credentials.
- Enter a Description of the SMTP Credentials in the dialog box.
- Click Generate SMTP Credentials. A user name and password is displayed.
- Copy the user name and password for your records and click Close.
- Create an Approved Sender
You must set up an approved sender for all “From:” addresses sending mail via Oracle Cloud Infrastructure or mail will be rejected. An approved sender is associated with a compartment and only exists in the region where the approved sender was configured.
- Go to OCI Console → Developer Services → Application Integration → Email Delivery.
- Create an approved sender email address under Approved Senders.
Click on the Configuration and note the SMTP Sending Information
- Configure Oracle Wallet for SSL
If you're sending emails over a secure connection (SSL/TLS), you need to configure an Oracle Wallet that stores the trusted certificates for secure communication.
- Download the SSL Root and Intermediate Certificates (.crt format) directly from https://www.digicert.com/kb/digicert-root-certificates.htm.
Root Certificate - DigiCert Global Root G2
Intermediate Certificate - DigiCert Global G2 TLS RSA SHA256 2020 CA1
- Create an auto-login Oracle Wallet
orapki wallet create -wallet /home/oracle/wallet -auto_login
- Add Certs (Root and Intermediate), downloaded in step 1. to the Wallet.
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle /DigiCertGlobalRootCA.crt"
orapki wallet add -wallet /home/oracle/wallet -trusted_cert -cert "/home/oracle/DigiCertSHA2SecureServerCA.crt"
Verify the Wallet
orapki wallet display -wallet /home/oracle/wallet
You may verify the certs using "echo |openssl s_client -starttls smtp -crlf -showcerts -connect <SMTP Server for e.g smtp.email.ca-toronto-1.oci.oraclecloud.com >:587"
- Send email through UTL_SMTP
Below is an example PL/SQL block to send an email using UTL_SMTP. It includes authentication and uses SSL/TLS.
SET SERVEROUTPUT ON;
DECLARE l_mailhost VARCHAR2(64) := 'smtp.email.us-ashburn-1.oci.oraclecloud.com'; -- Example SMTP server l_port NUMBER := 587; l_username VARCHAR2(500) := 'ocid1.user.oc1..aaaaaaaabbbbbbbbbbbbbbbb@ocid1.tenancy.oc1..aaaaaaaazzibbbbbbbbb.ct.com'; -- SMTP username l_password VARCHAR2(64) := 'xxxxxxxxxxx'; -- SMTP password l_from VARCHAR2(64) := 'aaaaaaaaa@abc.ca'; -- Approved sender email address l_to VARCHAR2(64) := 'bbbbbbbbb@abc.ca'; -- Recipient email address l_subject VARCHAR2(256) := 'Test Email from DBCS system'; l_message VARCHAR2(512) := 'This is a test email sent using UTL_SMTP.'; l_wallet_loc VARCHAR2(64) := 'file:/home/oracle/dbc/commonstore/wallets/ssl'; l_wallet_pwd VARCHAR2(64) := 'xxxxxxxxxxxxx'; l_mail_conn UTL_SMTP.connection;
PROCEDURE send_header(name IN VARCHAR2, header IN VARCHAR2) AS BEGIN UTL_SMTP.WRITE_DATA(l_mail_conn, name || ': ' || header || UTL_TCP.CRLF); END;
BEGIN -- Open a connection to the SMTP server l_mail_conn := utl_smtp.open_connection(host => l_mailhost, port => l_port, wallet_path => l_wallet_loc, wallet_password => l_wallet_pwd, secure_connection_before_smtp => FALSE);
-- Start TLS (if required by the SMTP server) UTL_SMTP.starttls(l_mail_conn);
-- Perform the SMTP handshake UTL_SMTP.ehlo(l_mail_conn, l_mailhost);
-- Authenticate UTL_SMTP.AUTH(c => l_mail_conn, username => l_username, password => l_password, schemes => 'PLAIN'); -- OCI Email delivery only support AUTH_PLAIN and STARTTLS (with TLS 1.2) to authenticate. Do not use AUTH_LOGIN, and utilizing it will result in failure.
-- Specify the sender and recipient UTL_SMTP.mail(l_mail_conn, l_from); UTL_SMTP.rcpt(l_mail_conn, l_to);
-- Write the email content (header + body) UTL_SMTP.open_data(l_mail_conn); send_header('From', '"Testing" <aaaaaaaaa@abc.ca>'); -- -- Approved sender email address send_header('Subject', l_subject);
UTL_SMTP.write_data(l_mail_conn, UTL_TCP.CRLF); -- Blank line separating headers from body UTL_SMTP.write_data(l_mail_conn, l_message); UTL_SMTP.close_data(l_mail_conn);
-- Close the SMTP connection UTL_SMTP.quit(l_mail_conn);
DBMS_OUTPUT.put_line('Email sent successfully.'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line('Error: ' || SQLERRM); END; / |
- Configure Mailx to Send Email Through Email Delivery
- Install mailx: If mailx is not already installed.
sudo yum install mailx -y
- Configure /etc/mail.rc:
Edit the mailx configuration file /etc/mail.rc to set up the SMTP server and credentials for OCI Email Delivery
set nss-config-dir=/etc/pki/nssdb/ set smtp-use-starttls set smtp-auth=plain set smtp=smtp.email.ca-toronto-1.oci.oraclecloud.com:587 set from=approvedsender@example.ca set smtp-auth-user=ocid1.user.oc1..aaao@ocid1.tenancy.oc1.bbb tx.com set smtp-auth-password=<password> |
From email address is the one that we registered as an approved sender.
The smtp-auth-user and smtp-auth-password are the credentials we saved in step 2.
- Test Sending an Email: Now, test if mailx is properly configured by sending a test email:
echo "This is a test email sent via OCI Email Delivery" | mailx -s "Test Email from OCI" recipient@example.com |
Possible errors
If you get an error(s) while running the above PLSQL code, below are the possible causes.
ORA-29024: Certificate validation failure: This error occurs if the SSL certificate isn't trusted by the Oracle Wallet. Ensure the proper CA certificates are imported.
ORA-24263: The certificate of the remote server does not match the target address: This happens if the SSL certificate name doesn’t match the server’s hostname.
- Missing Trusted Certificate: Oracle does not have the trusted certificate authority (CA) certificate for the SMTP server in its wallet.
- Expired or Invalid Certificate: The certificate presented by the SMTP server is expired or invalid.
- Incorrect Wallet Configuration: Oracle's wallet, which stores trusted CA certificates, is misconfigured or missing.
- DNS Mismatch: The domain name used to connect to the SMTP server does not match the Common Name (CN) or Subject Alternative Name (SAN) in the certificate.
- SSL/TLS Version Mismatch: The Oracle client is using an incompatible version of SSL/TLS for the SMTP server.
- Ensure the OCI Email Delivery service has the correct approved sender address.
- Check for any firewall issues or email service blocks.
- Verify SMTP credentials.
Summary
By following these steps, you'll be able to send emails securely from your OCI DB system using the UTL_SMTP package, with SSL enabled.