APEX_MAIL is the API package for sending emails from Oracle Application Express.
In order to be able to execute APEX_MAIL procedures and/or check the related views outside of Apex (from SQL Developer or from a scheduled job for instance), you need to initialize at least the Security Group ID that uniquely identifies an Apex workspace.
begin
APEX_UTIL.SET_SECURITY_GROUP_ID(APEX_UTIL.FIND_SECURITY_GROUP_ID('<workspace>'));
end;
If you are building the message content by means of translatable text messages retrieved via APEX_LANG.MESSAGE, you'll also need to initialize the application ID:
begin
apex_application.g_flow_id := <nnnnnn>;
end;
Assembling the various elements of the email message using text messages avoids hardcoding text into your procedures and allows you to translate the application into other languages easily.
When you call APEX_MAIL.SEND you are not really sending out anything yet because emails are normally transmitted in batches by a scheduled job running in the background and that's why when you check the view APEX_MAIL_QUEUE, you can see your messages queued up for transmission, waiting to be picked up by the job called APEX_XXYY00.ORACLE_APEX_MAIL_QUEUE. This job runs every 5 minutes on the minute starting from minute 00 by default.
APEX_MAIL_QUEUE implements a sort of VPD, such that a user in workspace A cannot see the emails queued by another user in workspace B and the same applies to APEX_MAIL_LOG. The Apex Instance Administrator can see all messages and delete them if necessary from page Home > Manage Instance > Mail Queue. The same applies to a user logged in as SYSTEM or SYS from a SQL client like SQL Developer.
You can force the transmission through the API APEX_MAIL.PUSH_QUEUE.
APEX_MAIL.PUSH_QUEUE will push all messages in the queue, not just yours. Pushing the queue too often might lead to the error:
ORA-30678: too many open connections
Please note that there is a hard limit of 16 TCP connections per database session.
Messages are removed from the queue either when the transmission succeeds or after 10 unsuccessful attempts and eventually they show up in APEX_MAIL_LOG. This means that a failing email message will pop up in APEX_MAIL_LOG between 50 and 55 minutes after it was originally queued, assuming the DBA didn't change the default frequency of the job. While the background job keeps retrying the transmission you can see the column APEX_MAIL_QUEUE.MAIL_SEND_COUNT incrementing (values are in range 0-9, where 0 means it hasn't been picked up yet), so you could keep an eye on it and take appropriate action before the message is eventually discarded.
Please note that you can DELETE rows from APEX_MAIL_QUEUE if you really need to do so.
If there is something wrong with the mail server configuration or the network connectivity or the email recipient(s) addresses, the column MAIL_SEND_ERROR (it's present in both APEX_MAIL_QUEUE and APEX_MAIL_LOG) will contain the relevant error message.
For instance, in case the mail server is unreachable, you'll most likely see the following error message:
ORA-29278: SMTP transient error: 421 Service not available
A typical mail server configuration error (see related blog entry):
ORA-29279: SMTP permanent error: 554 5.7.1 : Relay access denied
If multiple email addresses are specified using the wrong separator (see the related blog entry):
ORA-29279: SMTP permanent error: 501 Bad address syntax
Log entries in APEX_MAIL_LOG will be sitting there until the Apex Instance Administrator purges the log from the dedicated page (Home > Monitor Activity > Mail Log). That's why I wrote my own purge log logic (see the related article) in order to periodically remove the oldest entries.
See message translations for ORA-30678 and search additional resources.
No comments:
Post a Comment