Managing Emails in Oracle Application Express

Protect against the trauma of being responsible for an accidental spamming with a solid but flexible encapsulation strategy and PL/SQL.

By Steven Feuerstein

September 18, 2019

Does your application send out emails? Lots of emails?

Have you ever felt like someone punched you in the stomach when you realize that you mistakenly sent out hundreds or thousands of emails to your application users?

I have. It’s a terrible feeling. And these days, in the age of the EU’s General Data Protection Regulation (GDPR), there can be real consequences for invading the privacy of your users. This article explores how to make sure that, at least when you are developing and testing your code, you do not inadvertently spam your users.

I lead a team that develops and supports the Oracle Dev Gym, which offers classes, workouts, and quizzes on a variety of Oracle technologies. We send out lots of different kinds of emails to those players who have opted in for them, such as:

• Results of the quiz they just completed
• Confirmation of a sign-up for a class
• Reminder to take our weekly tournament quizzes
• Hourly reports to site admins with any new errors in our log
• Weekly activity summaries to quiz masters

The Dev Gym is an Oracle Application Express (Oracle APEX) app, so we are able to happily and easily take advantage of the APEX_MAIL package and its SEND procedure to do all the heavy lifting when it comes to emails. Under the covers, it uses UTL_SMTP to get its heavy lifting done.

Emails are, by default, placed in a queue, and a job named ORACLE_APEX_MAIL_QUEUE periodically sends all mail messages stored in the active mail queue.

Figure 1 shows a diagram of what our email workflow could look like:

Figure 1: Possible email workflow

In other words, wherever and whenever we need to send an email, whether it is from the quiz manager package, the class manager package, or the site admin utilities package, we could simply call APEX_MAIL.SEND directly.

As I say, we could take this approach. But that would be a really bad idea.

Here’s why: With multiple accesses to the “core” procedure that sends the email, it is difficult to put any kind of controls in place regarding email delivery and also more challenging to debug and enhance email-related code.

Consider the situation at the Dev Gym. We currently have 72,000 registered users, and of those, 30,000 have opted in for emails. We have development, test/stage, and production environments at the Dev Gym.

We set the following rule for code when it comes to emails:

In other words, we still need to see the emails to verify the format and other behaviors, but those emails should never “escape” from the development team.

If APEX_MAIL.SEND is called from dozens of locations in our code, we need to go to each of those locations and apply our rule. Therein lies madness and the inevitable mistakes that result in emails that spam recipients.

So, instead, we follow this guideline when writing email-related code:

Well, OK, not exactly never. No, just once. The only place we will call the APEX_MAIL.SEND procedure is from our very own send_email procedure, defined in the dg_comm_mgr package.

Our own send_email procedure, in turn, calls APEX_MAIL.SEND. With this approach, the flow of email requests then looks like Figure 2.

Figure 2: Email workflow with send_email

By following this rule, we ensure that APEX_MAIL.SEND is called in just one place in our entire code base: inside our own wrapper, the dg_comm_mgr.send_email procedure.

Here’ s a greatly simplified version of the send_email procedure:

PROCEDURE send_email (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
   l_email_id   INTEGER;
   l_text       CLOB;
   /* Just a placeholder for this demonstration */
   FUNCTION is_developer (user_id_in IN INTEGER)
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN TRUE;
   END;
   FUNCTION user_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      l_email_address  dg_users.email_address%TYPE;
   BEGIN
      SELECT email_address
        INTO l_email_address
        FROM dg_users
       WHERE user_id = user_id_in;
      RETURN l_email_address;
   END;
   FUNCTION send_to_email_address (user_id_in IN INTEGER)
      RETURN VARCHAR2
   IS
      c_email_address   dg_users.email_address%TYPE
                           := user_email_address (user_id_in);
   BEGIN
      RETURN CASE
                WHEN dg_util_pkg.in_production THEN c_email_address
                WHEN is_developer (user_id_in) THEN c_email_address
                ELSE dg_util_pkg.admin_email_address ()
             END;
   END;
BEGIN
   l_email_id :=
      apex_mail.send (
           p_to          => send_to_email_address (to_user_id_in),
           p_from        => 'noreply@oracle.com',
           p_subj        => subject_in,
           p_body        => l_text,
           p_body_html   => html_in,
           p_cc          => NULL,
           p_bcc         => NULL);
   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;
END;

Here are the highlights:

• We create a nested function to return the email address to which the email will be sent. Inside that function, we have all the logic to implement the requirement.

• The dg_util_pkg package contains a function that returns TRUE if we are currently running this code in production.

• Since the only call to apex_mail.send occurs within the send_email procedure, we are now 100% protected from accidentally sending out an email to users when that was not intended or desired.

• We don’t always push the email out of the queue. For example, when we are doing a batch email, we want to wait till everything is put into the queue, and then push it. If we are notifying a user of a specific event or accomplishment, we might want to push the email immediately.

So: nothing terribly fancy, no rocket science. Just another demonstration of how encapsulating low-level functionality to control access to that functionality provides flexibility and control that is otherwise very difficult to achieve.

Your Own Mail Queue

With the encapsulation layer in place, it becomes really easy to add more functionality to the email management features. For example, at the Dev Gym, we recently decided to implement our own email queue. We realized we needed this when there was a failure to send emails at one point and we had no easy way to recover.

We wanted, instead, to keep track of all emails we (tried to) send through APEX_MAIL.SEND, along with the actual HTML content of these emails. We could then resend the emails if there was a problem, verify that emails were produced and sent as expected, and confirm email content and formatting.

First we created a table to hold all the relevant information.

CREATE TABLE dg_emails (
   email_id       NUMBER GENERATED ALWAYS AS IDENTITY NOT NULL,
   email_sent     VARCHAR2 (1 BYTE) DEFAULT 'N',
   to_address     VARCHAR2 (500 BYTE),
   from_address   VARCHAR2 (500 BYTE),
   subject        VARCHAR2 (1000 BYTE),
   body           CLOB,
   body_html      CLOB,
   cc             VARCHAR2 (1000 BYTE),
   bcc            VARCHAR2 (1000 BYTE),
   attachment     BLOB,
   filename       VARCHAR2 (1000 BYTE),
   mime_type      VARCHAR2 (1000 BYTE),
   created_by     VARCHAR2 (132 BYTE) NOT NULL ENABLE,
   changed_by     VARCHAR2 (132 BYTE) NOT NULL ENABLE,
   created_on     DATE NOT NULL ENABLE,
   changed_on     DATE NOT NULL ENABLE,
   email_run_id   NUMBER (*, 0)
);

Then we added a procedure to load each email into our queue.

PROCEDURE write_to_queue (email_sent            VARCHAR2,
                          to_address            VARCHAR2,
                          from_address          VARCHAR2,
                          subject               VARCHAR2,
                          body                  CLOB,
                          body_html             CLOB,
                          cc                    VARCHAR2,
                          bcc                   VARCHAR2,
                          attachment            BLOB,
                          filename              VARCHAR2,
                          mime_type             VARCHAR2,
                          email_run_id_in  IN   INTEGER DEFAULT  NULL,
                          email_id_out     OUT  INTEGER)
IS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO dg_emails  (email_sent,
                           to_address,
                           from_address,
                           subject,
                           body,
                           body_html,
                           cc,
                           bcc,
                           attachment,
                           filename,
                           mime_type,
                           email_run_id)
        VALUES ('N',
                write_to_queue.to_address,
                write_to_queue.from_address,
                write_to_queue.subject,
                write_to_queue.body,
                write_to_queue.body_html,
                write_to_queue.cc,
                write_to_queue.bcc,
                write_to_queue.attachment,
                write_to_queue.filename,
                write_to_queue.mime_type,
                write_to_queue.email_run_id_in)
     RETURNING email_id
          INTO email_id_out;

   COMMIT;
END;

Note that it is an autonomous transaction. We do that so even if something went wrong after this procedure completed, we would still have a record of the attempted email.

Of course, anytime you have a queue, you have to pay attention to its size and purge it now and then, so we added this procedure to the dg_comm_mgr package:

PROCEDURE purge_queue (n_days_in IN INTEGER DEFAULT 7)
IS
BEGIN
   DELETE FROM dg_emails
         WHERE created_on < SYSDATE - n_days_;
END;

If we need to retry sending the email, we now have an overloading of the original send_email procedure. It queries the email details from the queue and pushes email out, using APEX_MAIL.SEND.

PROCEDURE send_email (email_id_in        IN INTEGER,
                      push_to_queue_in   IN BOOLEAN DEFAULT FALSE)
IS
   l_apex_id   INTEGER;
   l_email     dg_emails%ROWTYPE;
BEGIN
   SELECT *
     INTO l_email
     FROM dg_emails
    WHERE email_id = email_id_in;

   l_apex_id :=
      apex_mail.send (p_to          => send_to_email_address
                     (l_email.to_address),
                      p_from        => l_email.from_address,
                      p_subj        => l_email.subject,
                      p_body        => l_email.body,
                      p_body_html   => l_email.body_html,
                      p_cc          => l_email.cc,
                      p_bcc         => l_email.bcc);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;

   mark_email_sent (email_id_in);
END send_mail;

A sharp reader will now be thinking, “Wait a minute! You just violated your encapsulation rule. Right there in your send_email overloading, you have a second call to APEX_MAIL.SEND. Liar!”

Well, you know about rules. They are made to be broken. It is true; we call APEX_MAIL.SEND in both send_email procedures. I offer these observations (you can call them excuses if you’ d like):

1. Both invocations are in the dg_comm_mgr package. This makes it easier to keep things from getting out of control.

2. It is a very special-purpose procedure, distinct from the other send_email procedure; in a way, it is “lower-level.”

No doubt about it—we could refactor the original send_email procedure to incorporate the logic of the queue-driven send process, but we would end up with code that is harder to read and maintain.

So we break a rule to follow other important software quality guidelines.

We do, however, need to go back to the original send_email procedure and make sure that the email information is put into the queue before the attempt is made to send it out into the world.

PROCEDURE send_email (
   to_user_id_in      IN INTEGER,
   subject_in         IN VARCHAR2,
   html_in            IN CLOB,
   push_to_queue_in   IN BOOLEAN DEFAULT TRUE)
IS
...
BEGIN
   write_to_queue (
     email_sent     => 'N',
     to_address     => send_to_email_address (to_user_id_in),
     from_address   => 'noreply@oracle.com',
     subject        => subject_in,
     body           => l_text,
     body_html      => html_in,
     cc             => NULL,
     bcc            => NULL,
     email_id_out   => l_email_queue_id);
   l_email_id :=
      apex_mail.send (p_to          => send_to_email_address
                     (to_user_id_in),
                      p_from        => 'noreply@oracle.com',
                      p_subj        => subject_in,
                      p_body        => l_text,
                      p_body_html   => html_in,
                      p_cc          => NULL,
                      p_bcc         => NULL);

   IF push_to_queue_in
   THEN
      apex_mail.push_queue;
   END IF;
   mark_email_sent (l_email_queue_id);
END;

Hope for the Best, Plan for the Worst

All it takes is one careless enhancement or bug fix, and you’ve suddenly got company lawyers and GDPR experts knocking on your door. You don’t want to be that programmer.

If you have not already protected your users and yourself from the trauma of an accidental spamming, I hope that you can learn from and use the approach we’ve taken in the Oracle Dev Gym.

Think also about other aspects of your application in which you have multiple pathways to the same or similar functionality, especially when involving invocations of procedures and functions provided by Oracle.

Apply the same techniques shown in this article proactively, and you are likely to save yourself from some nightmarish episodes in the future.

Next Steps

READ Oracle Application Express 19c APEX_MAIL documentation.

READ an Oracle Application Express 19c APEX_MAIL documentation on sending email from an application.

WORK OUT at the Oracle Dev Gym.

Illustration by Wes Rowell