Our client was eager to roll out the subscription feature of Oracle APEX Interactive Reports.
Daily reports delivered to their inboxes without having to log in to any applications, was something several users were looking forward to.
It turned out there were a few things we learned along the way that could be helpful to some of you troubleshooting issues with email delivery of your subscriptions.
Read on if this is you!
1) OCI Email Delivery Services
The recommended approach for sending email from APEX on OCI is to use OCI Email Delivery services.
Detailed instructions on how to configure Oracle APEX to send email through Email Delivery Services can be found here. It also includes a link on how to manage your approved sender list. You might also want to check out Josh Nason’s post entitled 5 must-know facts about approved senders in OCI Email Delivery.
2) Configure your application to use the approved sender
In the Oracle APEX builder, navigate to your application’s properties.
There, you will find an item called: Application Email From Address. Enter one of the addresses you added to your list of approved senders above. This will be your default sender on the subscriptions unless you specify an override.
3) Understand email size limitations
At the time of writing this post, the default maximum size for an email sent from OCI using OCI Email Delivery is 2 Mb. It is possible to request a service increase, but be aware of this default setting.
Up until APEX 21.1, subscribed reports are delivered as attachments in HTML format. Know that these can grow quite rapidly in size. As of APEX 21.2, report subscription functionality has been extended and now allows users to select the format in which they receive the email based on the formats the developer has made available for the IR download (CSV, HTML, Excel, or PDF).
Unless you plan on requesting the service increase, you should try to design your reports in such a way as to ensure they will not exceed the maximum byte size.
If they do, they actually get stuck in the email queue, leaving the user to think the process simply “didn’t work” which we learned can be quite a frustrating experience.
4) Missing SPF (Sender Policy Framework) record
So you’ve set up your list of approved senders on OCI. You’ve set at least one of those up as your Application Email Address. You’ve either ensured your reports are less than 2Mb in size, or you’ve requested a service limit increase. There are no errors in your apex_mail_queue. But your users still aren’t getting the emails?
Some domains will block emails that are attempting to ‘spoof’ their domain. So although it will look like the email was successfully sent from your application, without an SPF record, users might never see the emails, even in their junk or spam folders.
You’ll definitely want to implement SPF by publishing a special TXT record to your domain’s DNS records to ensure emails are not blocked as phishing or spoofing attempts.
5) Subscription timing
This one was fun.
Because the IR subscription settings allow users to select the time they would like to receive the emails in their inboxes, while we were testing during a working session, we would make small changes and set the subscription to fire in a few minutes.
The emails would not come.
After a fair amount of fussing about and hair pulling, we understood that the subscription emails only fire twice an hour, on the half and full hours, whichever is closest to the time selected by the user.
6) Subscribe to an email queue error report!
Although none of the above is rocket science, the various issues our client faced when setting up report subscriptions (approved sender, default application email sender, maximum bytes exceeded error, etc.) were quickly visible by monitoring the email queue view.
Anytime an application sends email, we recommend monitoring the email queue view for any issues involving emails. By querying the apex_mail_queue, we were able to see the issues related to authorized senders and file size.
It isn’t always easy for an application administrator to monitor email errors in production, which is why we often recommend creating an interactive report on apex_mail_queue (select mail_to, mail_subj, mail_message_created, mail_send_error from apex_mail_queue where mail_send_error is not null) and allowing application administrators to subscribe to it.
Pretty meta, right?
If you like that tip, you should also check out episode #55 of APEX Instant Tips: Oracle APEX – Built with <3 using Oracle APEX
Subscribing to daily reports is a great way for users to stay on top of key information without having to log in to the application.
Application administrators or developers subscribing to error logs and performance reports are some of our favorite use cases.
Hopefully, this post was helpful if you’re facing a few issues getting your subscriptions going.
