Wipe APEX mail queue
Refreshing any of our non-prod environments (e.g. dev, test, etc.) with a clone from production is a fairly regular process at my client. A recurring issue with this is emails: we’ve had occasion where users have received a second copy of an email immediately after the clone has completed. This was confusing because they thought the event that had triggered the email actually occurred twice.
As it turns out, the duplicate emails were caused by the fact that the emails happened to be waiting in the APEX mail queue in production at the time of the export. After the export, the APEX mail queue was processed normally in production and the users received their emails as expected; after the clone was completed, the database jobs were restarted in the cloned environment which duly processed the emails sitting in the cloned queue and the users effectively got the same emails a second time.
What’s worse, if the same export were to be used for multiple clones, the users might get the same emails again and again!
A good way to solve this sort of issue would be to isolate the non-prod environments behind a specially configured mail server with a whitelist of people who want (and expect) to get emails from the non-prod systems. We don’t have this luxury at this client, however.
Instead, we have a post_clone.sql script which is run by the DBAs immediately after creating the clone. They already stop all the jobs by setting job_queue_processes=0.
In case the mail queue happens to have any emails waiting to be sent, the post clone script now includes the following step:
begin
*** WARNING: DO NOT RUN THIS IN PRODUCTION! ***
for r in (
select workspace_id
,workspace
from apex_workspaces
) loop
apex_application_install.set_workspace_id (r.workspace_id);
apex_util.set_security_group_id
(p_security_group_id => apex_application_install.get_workspace_id);
delete apex_mail_queue;
end loop;
commit;
end;
/
This script is run as SYS but it could also be run as SYSTEM or as APEX_nnnnnn, depending on your preference.
ADDENDUM: Overriding the From Email Address
Christian Neumüller commented that an additional technique that might be useful is to override the From (sender) email address to indicate which environment each email was sent from. To do this, run something like the following:
begin
apex_instance_admin.set_parameter('EMAIL_FROM_OVERRIDE',
'apex-' || sys_context('userenv','db_name') || '@mydomain');
end;
I’ve tested this in APEX 19.1 and it seems to work fine. Regardless of the p_from parameter that the code passes to apex_mail.send, the EMAIL_FROM_OVERRIDE email address is used instead.
Note that this is currently undocumented, so this may stop working or change in a future release.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
Christian Neumüller
4 July 2019 - 10:02 pm
Starting with APEX 19.1, there is also the (not yet documented) instance parameter EMAIL_FROM_OVERRIDE. If set, it is used instead of the p_from parameter value. You could set it on clones to indicate it’s coming from a different server than the default, in addition to emptying the mail queue, which is a good idea.
Jeffrey Kemp
4 July 2019 - 10:06 pm
That’s good to know, thanks Christian. I’m pretty sure we’d use that, I’ll give it a try.
It would be great to also have an EMAIL_TO_OVERRIDE parameter which would redirect all emails to a single recipient.
Christian Neumüller
4 July 2019 - 10:48 pm
Interesting, what’s your use case for this?
Jeffrey Kemp
4 July 2019 - 10:55 pm
I have a wrapper for all emails that my code sends so that in the dev environment I can have all emails sent to my email address. It’s handy when testing a notifications system which needs to send emails to multiple recipients, but the primary use case is to ensure that no emails go out to real email addresses from the non-prod systems. Where I’m working they’re very sensitive about it, and the occasional misstep (e.g. duplicate emails, emails sent from dev / test systems to real users) has caused considerable angst.
Christian Neumüller
4 July 2019 - 11:16 pm
Thanks, makes sense. I made a note.
Jorge Rimblas
6 July 2019 - 7:16 am
+1 for a EMAIL_TO_OVERRIDE
I build exactly the same type of wrapper to protect people from dev|test “spam”
Paolo Marzucco
9 July 2019 - 11:08 pm
+1 as well for EMAIL_TO_OVERRIDE, we also have to resort to several tricks to avoid sending mail to real people from dev/test instances…
Gemma Wood
24 January 2020 - 12:34 am
+1 as well for EMAIL_TO_OVERRIDE. I have the same sort of wrapper as everyone else, but I would be a lot more comfortable with something like this. I also think that the mail queue needs 1) an API to delete/update records and 2) Some sort of webhook for if a message cannot be delivered to avoid the manual intervention.
Andy Hardy
27 December 2021 - 3:56 am
+1 for EMAIL_TO_OVERRIDE and EMAIL_FROM_OVERRIDE. I’d also like a ‘footer’ to *really* emphasise where the mail is from. Customers have enough to contend with without having to decide if they need to make a decision based on an email that should be safely ignored.
Jeffrey Kemp
27 December 2021 - 10:32 am
Idea raised: https://apex.oracle.com/ideas/FR-2265