Integrate your APEX application with AWS Simple Email Service (SES)

I have recently worked on migrating an APEX application to AWS (using Oracle RDS). The application had a custom email notification functionality that utilized UTL_MAIL and APEX_MAIL through a regular on-prem SMTP server. However, the goal was to completely remove the APEX application along with underlying Oracle DB, APEX static files, APEX environment, and all existing application’s functionality (obviously including Email notifications) from source “on-prem” system and seamlessly migrate everything over to AWS.

Aside from the challenges I had to overcome while migrating the APEX application to AWS/RDS due to some RDS limitations and the lack of comprehensive technical documentation, I had to figure out a way to make email notifications continue to work without using the on-prem SMTP server. Since the whole idea was to migrate APEX from on-prem environment to the cloud (AWS in this case), I thought that the best approach would be to explore AWS alternatives to using regular SMTP server. Luckily, I found a pretty nice AWS service called Amazon Simple Email Service (SES), which you basically can think of it as an SMTP-as-a-Service (with some negligible limitations).

The following is a step by step guide on how to integrate SES with your APEX application. The APEX application can be hosted on-prem or in a cloud of your choice. In this example, I have used Oracle RDS as application’s database, APEX 5.0 (you need to spin up a separate EC2 to configure ORDS when you use AWS RDS), and SES. As mentioned above, RDS comes with some limitations that make things a little more difficult. However, if you are installing full Oracle instance on an EC2 (or even on-prem) and have access to underlying file system, then some of the RDS-specific steps below can be skipped.

1) In your AWS instance, go to Services, then Simple Email Service. Under SMTP Settings you should find your SES SMTP settings. These settings (along with your instance-specific SMTP credentials) will be needed in the following steps. The setting should look something like the following:

Server Name: email-smtp.us-east-1.amazonaws.com
Port: 25, 465 or 587
Use Transport Layer Security (TLS): Yes
Authentication: Your SMTP credentials - see below


2) Since SES uses Transport Layer Security (TLS), you need to create an Oracle Wallet to store your SES keys/certificate. This is where RDS becomes more challenging since you don’t have access to underlying RDS file system. In this case, you should create your Oracle Wallet on a separate “full” Oracle instance then copy it over to your RDS instance. If you do not already have an Oracle instance that you can utilize to create the wallet, you can spin up a free XE database on your local machine. Again, you only need this instance to create the SES wallet, it won’t be needed afterwards. I will refer to this DB as “source DB” going forward. The commands below are based on an Oracle DB installed on Windows OS, so adjust as needed if you use other OS.

• Create an Oracle directory for SES wallet on your source DB
As SYS user:

CREATE OR REPLACE DIRECTORY SES_WALLET AS ' C:\app\oracle\product\12.1.0\dbhome_1\owm\wallets\ses_wallet\';)

This creates an Oracle Directory named SES_WALLET (you can name it anything you want) that is mapped to C:\app\oracle3\product\12.1.0\dbhome_1\owm\wallets\ses_wallet

• Download SES certificate
In your browser (I prefer Firefox, but you can use any browser), run the following URL:

https://email-smtp.us-east-1.amazonaws.com/

then download root certificate only (one on top, not Chain) as X.509 Cert (PEM). I saved it as “AmazonRootCA1.crt” in “C:\Oracle12\certs\”

• Create Oracle wallet in source DB

I created a wallet using orapki utility with auto_login option enabled:

orapki wallet create -wallet C:\app\oracle\product\12.1.0\dbhome_1\owm\wallets\ses_wallet -pwd -auto_login

Add downloaded SES certificate to the SES_WALLET wallet:

orapki wallet add -wallet C:\app\oracle\product\12.1.0\dbhome_1\owm\wallets\ses_wallet -trusted_cert -cert "C:\Oracle12\certs\AmazonRootCA1.crt" -pwd

• Create an Oracle directory for SES wallet on your RDS instance:

As Master user:

              exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'SES_WALLET');

In this case, I named the new directory “SES_WALLET” as well (but it can be named anything you want).

• Create a DB link from your source DB to RDS DB

create database link to_rds_db_link connect to <your RDS Master user> identified by <your RDS Master user pwd>
using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= <your RDS endpoint>)(PORT=<your RDS DB port, default is 1521>))(CONNECT_DATA=(SID=<your RDS DB SID>)))';

This creates a DB link named “to_rds_db_link” from source DB to RDS DB

• Copy wallet from source DB to RDS
Run the following snippet in your source DB, and adjust where needed (ex. DB link name, wallet name…etc.):

declare

  lfh utl_file.file_type;
  rfh utl_file.file_type@to_rds_db_link;
  ldata raw(32767);

begin
  -- open file handles
  lfh := utl_file.fopen(location=>'SES_WALLET', filename=>'ewallet.p12', open_mode=>'rb');
  rfh := utl_file.fopen@to_rds_prod(location=>'SES_WALLET', filename=>'ewallet.p12',     open_mode=>'wb');
  -- iterate local file and write it to remote
  begin
    loop
      begin
        utl_file.get_raw(lfh, ldata, 32767);
        utl_file.put_raw@to_rds_db_link(rfh, ldata, true);
      exception
        when no_data_found then
          exit;
      end;
    end loop;
  end;
  -- close file handles
  utl_file.fclose(lfh);
  utl_file.fclose@to_rds_db_link(rfh);  

exception 
  -- exception handling and making sure code does not leak file handles
  when others then
    utl_file.fclose(lfh);
    utl_file.fclose@to_rds_db_link(rfh);
    raise;
end;

3) Verify wallet in RDS:
Run the following query as Master user:

select DIRECTORY_PATH 
from DBA_DIRECTORIES 
where DIRECTORY_NAME= 'SES_WALLET';

The query should return SES wallet’s directory path in RDS which will be needed in step #5 below.

4) Since UTL_MAIL does not support TLS, you should consider using UTL_SMTP instead. In my case, the APEX application used UTL_MAIL so I created a wrapper package (UTL_SES_MAIL) with same signature as UTL_MAIL, and wrote the underlying code using UTL_SMTP. This way, I only had to replace references to UTL_MAIL with UTL_SES_MAIL across the application’s code.

create or replace PACKAGE UTL_SES_MAIL AS 

procedure send (sender in varchar2,
    recipients in varchar2,
    cc  varchar2 default null,
    bcc varchar2 default null,
    subject in varchar2,
    message in varchar2);
    
END UTL_SES_MAIL;

CREATE OR REPLACE PACKAGE BODY UTL_SES_MAIL
AS
   PROCEDURE send (sender       IN VARCHAR2,
                   recipients   IN VARCHAR2,
                   cc           IN VARCHAR2 DEFAULT NULL,
                   bcc          IN VARCHAR2 DEFAULT NULL,
                   subject      IN VARCHAR2,
                   MESSAGE      IN VARCHAR2)
   AS
      l_mailhost          VARCHAR2 (64);
      l_ses_user_name     VARCHAR2 (64);
      l_ses_pwd           VARCHAR2 (64);
      l_mail_connection   UTL_SMTP.connection;
      l_wallet_loc        VARCHAR2 (100);
      l_wallet_pwd        VARCHAR2 (20);
      l_reply             UTL_SMTP.REPLY;
      l_timestamp         VARCHAR2 (45);
      l_boundary          VARCHAR2 (50) := '----=*#abc1234321cba#*=';
      l_port              NUMBER;
      l_raw_data_sub      RAW (32767);
   BEGIN
      l_timestamp := TO_CHAR (SYSTIMESTAMP, 'DD Mon YYYY HH24:MI:SS');

      l_mailhost := ' email-smtp.us-east-1.amazonaws.com'; -- SES SMTP Host as provided by AWS

      l_port := 587; -- SES SMTP Port as provided by AWS

      l_ses_user_name := '???'; -- SES SMTP Username as provided by AWS

      l_ses_pwd := '???'; -- SES SMTP Password as provided by AWS

      l_wallet_loc := 'SMTP_WALLET'; -- the SES wallet name

      l_wallet_pwd := '???'; -- the SES wallet password

      l_mail_connection :=
         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);

      l_reply := UTL_SMTP.starttls (l_mail_connection);

      UTL_SMTP.AUTH (c          => l_mail_connection,
                     username   => l_ses_user_name,
                     password   => l_ses_pwd,
                     schemes    => 'LOGIN');

      UTL_SMTP.helo (l_mail_connection, l_mailhost);
      UTL_SMTP.mail (l_mail_connection, sender);

      IF (recipients IS NOT NULL)
      THEN
         FOR i IN (    SELECT DISTINCT REGEXP_SUBSTR (recipients,
                                                      '[^,]+',
                                                      1,
                                                      LEVEL)
                                          AS to_email_address
                         FROM DUAL
                   CONNECT BY REGEXP_SUBSTR (recipients,
                                             '[^,]+',
                                             1,
                                             LEVEL)
                                 IS NOT NULL
                     ORDER BY 1)
         LOOP
            UTL_SMTP.rcpt (l_mail_connection, i.to_email_address);
         END LOOP;
      END IF;

      IF (cc IS NOT NULL)
      THEN
         FOR i IN (    SELECT DISTINCT REGEXP_SUBSTR (cc,
                                                      '[^,]+',
                                                      1,
                                                      LEVEL)
                                          AS to_email_address
                         FROM DUAL
                   CONNECT BY REGEXP_SUBSTR (cc,
                                             '[^,]+',
                                             1,
                                             LEVEL)
                                 IS NOT NULL
                     ORDER BY 1)
         LOOP
            UTL_SMTP.rcpt (l_mail_connection, i.to_email_address);
         END LOOP;
      END IF;

      BEGIN
         UTL_SMTP.open_data (l_mail_connection);
         UTL_SMTP.write_data (l_mail_connection,
                              'Date: ' || l_timestamp || UTL_TCP.crlf);
         UTL_SMTP.write_data (l_mail_connection,
                              'To: ' || recipients || UTL_TCP.crlf);
         UTL_SMTP.write_data (l_mail_connection,
                              'CC: ' || cc || UTL_TCP.crlf);
         UTL_SMTP.write_data (l_mail_connection,
                              'bcc: ' || bcc || UTL_TCP.crlf);
         UTL_SMTP.write_data (l_mail_connection,
                              'From: ' || sender || UTL_TCP.crlf);
         l_raw_data_sub :=
            UTL_RAW.cast_to_raw ('Subject: ' || subject || UTL_TCP.crlf);
         UTL_SMTP.write_raw_data (l_mail_connection, l_raw_data_sub);
         UTL_SMTP.write_data (l_mail_connection,
                              'MIME-Version: 1.0' || UTL_TCP.crlf);
         UTL_SMTP.write_data (
            l_mail_connection,
               'Content-Type: multipart/mixed; boundary="'
            || l_boundary
            || '"'
            || UTL_TCP.crlf
            || UTL_TCP.crlf);


         IF MESSAGE IS NOT NULL
         THEN
            UTL_SMTP.write_data (l_mail_connection,
                                 '--' || l_boundary || UTL_TCP.crlf);
            UTL_SMTP.write_data (
               l_mail_connection,
                  'Content-Type: text/html; charset="iso-8859-1"'
               || UTL_TCP.crlf
               || UTL_TCP.crlf);
            UTL_SMTP.write_data (l_mail_connection, MESSAGE);
            UTL_SMTP.write_data (l_mail_connection,
                                 UTL_TCP.crlf || UTL_TCP.crlf);
         END IF;

         UTL_SMTP.write_data (l_mail_connection,
                              '--' || l_boundary || '--' || UTL_TCP.crlf);
         UTL_SMTP.close_data (l_mail_connection);
      EXCEPTION
         WHEN OTHERS
         THEN
            --  consider capturing exception detailed error message in your error log
            UTL_SMTP.quit (l_mail_connection);
      END;
   END send;
END UTL_SES_MAIL;

5) If your application uses APEX_MAIL to send emails, you need to configure your APEX workspace mail settings to use SES.

6) SES requires sender email IDs (FROM field) to be verified first. Otherwise, your emails won’t be delivered. Recipient email IDs do not need to be verified though.
Also, your AWS instance will have SES in sandbox mode by default (which comes with some limitations). Consider switching to full SES in production environments. You can request that through an AWS service ticket.

7) Consider configuring ACLs and grants (Ex. for UTL_SMTP and UTL_SES_MAIL packages) in your application’s schema. For example, the following configures required ACLs to allow your application schema and APEX to communicate with SES SMTP server:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
acl => 'smtp-ses-permissions.xml',
description => 'Permissions for SES SMTP',
principal => 'PUBLIC',
is_grant => TRUE,
privilege => 'connect'
);
COMMIT;

--create privilege for your APEX_xxxxxx schema (ex. APEX_050000)
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/smtp-ses-permissions.xml','APEX_050000', TRUE, 'connect');

--create privilege for your application parsing schema (ex. POC_APP)
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('/sys/acls/smtp-ses-permissions.xml','POC_APP', TRUE, 'connect');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
acl => 'smtp-ses-permissions.xml',
host => 'email-smtp.us-east-1.amazonaws.com',
lower_port => 587, -- I think only port 587 worked in my case
upper_port => null);
COMMIT;
END;
/

Your application should now be able to use APEX_MAIL and UTL_SES_MAIL (or whatever you name your custom email package) to send email notifications through AWS SES.