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
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
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.
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.
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.
4 July 2019 - 10:48 pm
Interesting, what’s your use case for this?
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.
4 July 2019 - 11:16 pm
Thanks, makes sense. I made a note.
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”
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…
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.
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.
27 December 2021 - 10:32 am
Idea raised: https://apex.oracle.com/ideas/FR-2265