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.