Send Email from OCI Email Delivery Service Through UTL_SMTP

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.

1. Set Up the IAM Policy

A user must be assigned to a group with permission to manage approved senders

Allow group to use approved senders in compartment

2. 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.

1. 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.         
2. Under the Resources, click SMTP Credentials.
3. Click Generate SMTP Credentials.
4. Enter a Description of the SMTP Credentials in the dialog box.
5. Click Generate SMTP Credentials. A user name and password is displayed.
6. Copy the user name and password for your records and click Close.

3. 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.

1. Go to OCI Console → Developer Services → Application Integration → Email Delivery.
2. Create an approved sender email address under Approved Senders.

Click on the Configuration and note the SMTP Sending Information

4. 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.

1. 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

2. Create an auto-login Oracle Wallet

orapki wallet create -wallet /home/oracle/wallet -auto_login

3. 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 :587"

5. 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 </aaaaaaaaa@abc.ca>

                  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;

/

6. Configure Mailx to Send Email Through Email Delivery 

1. Install mailx: If mailx is not already installed.

sudo yum install mailx -y

2. 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=

           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.

3. Test Sending an Email: Now, test if mailx is properly configured by sending a test email:

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.

1. Missing Trusted Certificate: Oracle does not have the trusted certificate authority (CA) certificate for the SMTP server in its wallet.
2. Expired or Invalid Certificate: The certificate presented by the SMTP server is expired or invalid.
3. Incorrect Wallet Configuration: Oracle's wallet, which stores trusted CA certificates, is misconfigured or missing.
4. 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.
5. SSL/TLS Version Mismatch: The Oracle client is using an incompatible version of SSL/TLS for the SMTP server.
6. Ensure the OCI Email Delivery service has the correct approved sender address.
7. Check for any firewall issues or email service blocks.
8. 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.

Want to find out more about how you can get the best out of OCI? Get in touch with DSP-Eclipsys today to explore better solutions for your business.