APEX_MAIL package and views quick reference

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

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.

ORA-29279: SMTP permanent error: 554 5.7.1 Relay access denied

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

A quick note in case one is getting the following exception while using APEX_MAIL.SEND procedure.

ORA-29279: SMTP permanent error: 554 5.7.1 : Relay access denied

ORA-29279 is a sort of catch-all exception for a whole class of SMTP errors, so the SMTP error code (in this case 554) followed by the textual description is what really matters. Some time ago i wrote another blog posting for a different situation where ORA-29279 was returned.

In this case the 554 Relay access denied error was caused by the setup of POSTFIX on one virtual machine acting as a test SMTP server, which was not configured to accept mail relaying from IP addresses other than the local machine (127.0.0.1/8).
After adding the IP address of the database server running Oracle Application Express to the list of "trusted" addresses, i could eventually send the emails stuck in the queue.
In case you are dealing with POSTFIX too, the relevant parameter is mynetworks.
You can read more about the setting of this parameter (and associated ones) in the POSTFIX documentation.

See message translations for ORA-29279 and search additional resources.

simple pager for Apex 3.0

Although i can claim that in the last 25 years i never got a phone call in the middle of the night for job reasons, i like to know as soon as possible when a major problem has occurred, especially if the error condition prevents users from doing their work.

However, if the application users are anonymous visitors of a web site, it can be difficult, if not impossible, to be contacted timely in case of problems that may arise for a variety of reason, including bugs at different levels, service outages, networking problems, unexpected usage patterns and so on.

If you are an Oracle Application Express developer or administrator you know that in the Monitor Activity section there is a useful built-in report displaying a list of unhandled exceptions that have occurred in the specified time frame, set by default to 1 day.

This report covers errors in the Apex domain, so we won't find any entries if the http server is down, that is when a user got a HTTP-404 error, but we will find entries if an Apex user got a broken report caused by an illegal parameter value, when an Apex process failed and the error was not trapped inside the program, including many other database related issues like a constraint violation or an exceeded tablespace quota that prevented the successful termination of a DML statement, always speaking in the context of Apex applications.

Clearly this approach implies an active participation on our side, because we must explicitly go to the Monitor Activity Page and run the report to know the information, and we know how easy is to forget a task when we have a busy agenda.

What I'm going to suggest here is a more passive approach that lets the computer do the work for us, that is in the end why these damned machines were invented in the first place.

The idea is to have a scheduled job that collects the information and sends us simple reports in form of emails at regular intervals.

Moreover, if you are using a mobile service provider integrating email and SMS, you can easily set up an automated paging system as i did.

And now for the real stuff.

As of Oracle Application Express version 3.0, we have finally all the required bits to carry out this task using supported objects, therefore we no longer need to have special privileges granted for accessing internal objects, in other words the most typical and least appreciated phrase in IT's history: "with the next software release you'll able to do that" has been fulfilled eventually.

Keeping in mind that that Oracle Application Express can still run on top of Oracle 9.2, where DBMS_SCHEDULER is not available, but DBMS_JOB is, i wrote a simple package that allows you to monitor the situation at regular intervals.

Of course the usual disclaimers apply, if you run a nuclear plant don't look at me if anything goes wrong with this program.

Note also that if you (or your DBA) revoked the execution privilege on DBMS_JOB package from PUBLIC (or from the schema user you are trying to compile the package into), the package compilation will fail.
If DBMS_JOB is disabled, that is parameter JOB_QUEUE_PROCESSES is set to zero (0), the pager will not be started, informing you via DBMS_OUTPUT that background jobs are disabled.

The procedures communicate messages via DBMS_OUTPUT and, hopefully, are self-explanatory.

I strongly recommend to perform these tasks from within the Apex environment, but you can do the same from sqlplus too, remembering to COMMIT whenever necessary.

The installation is straightforward, just download the SIMPLE_PAGER for Oracle Application Express 3.0 package and compile it in you schema.

Usage notes are provided in the package specification, however, once the packages have been compiled in the schema, the pager can be started within the next minute as follows:

begin
simple_pager.start_pager(
email       => '...',         -- comma separated list of recipients
email_from  => '...',         -- email of the sender
interval    => 4              -- hours between each alert
);
end;

For instance if you prefer to receive the notifications periodically, on the hour, every 2 hours, starting from 12:00:00 (supposing now it's some time between 00:00 and 11:59:59), then you can specify the optional parameter next_alert, as follows:

begin
simple_pager.start_pager(
email       => '...',                    -- comma separated list of recipients
email_from  => '...',                    -- email of sender
interval    => 2,                        -- hours between each alert
next_alert  => trunc(sysdate) + 1/2      -- start at noon
);
end;

The pager can run without problems in a hosted environment, where each different workspace/user can run its own pager without interfering with others.

Hope it helps.

See more articles about Oracle Application Express or download tools and utilities.

ORA-29279: SMTP permanent error: 501 Bad address syntax

While checking if procedure APEX_MAIL.SEND could send out a message to multiple users and being too lazy to open up the on-line help window and check what the documentation says, i made an attempt for sending a message to multiple recipients as follows:

begin
apex_mail.send(
p_to       => 'first@somedomain.com;second@somedomain.com',
p_from     => 'apex@someaddress.com',
p_body     => 'test message'
);
apex_mail.push_queue;
end;

But looking at view APEX_MAIL_QUEUE, i saw the message below in column MAIL_SEND_ERROR

ORA-29279: SMTP permanent error: 501 Bad address syntax

This is caused by the semicolon (;) used for separating the addresses.
Use commas (,) instead.

To delete undeliverable messages from APEX_MAIL_QUEUE, just execute:

delete from APEX_MAIL_QUEUE
where MAIL_SEND_ERROR is not null;

See message translations for ORA-29279 and search additional resources.

---

ORA-29279: Errore permanente SMTP:
ORA-29279: error permanente de SMTP:
ORA-29279: error permanent de SMTP:
ORA-29279: erreur permanente SMTP :
ORA-29279: Permanenter SMTP-Fehler:
ORA-29279: μόνιμο σφάλμα SMTP:
ORA-29279: permanent SMTP-fejl:
ORA-29279: Permanent SMTP-fel:
ORA-29279: permanent SMTP-feil:
ORA-29279: pysyvä SMTP-virhe:
ORA-29279: SMTP állandó hiba:
ORA-29279: eroare permanentă SMTP:
ORA-29279: Permanente SMTP-fout:
ORA-29279: erro permanente de SMTP:
ORA-29279: erro permanente de SMTP:
ORA-29279: Постоянная ошибка SMTP:
ORA-29279: Permanentní chyba SMTP:
ORA-29279: trvalá chyba SMTP:
ORA-29279: trwały błąd SMTP:
ORA-29279: SMTP kalıcı hatası: 

PLS-00307: too many declarations of XYZ match this call

While doing some tests before publishing my previous posting, i came across the following exception that is to be rarely seen when calling overloaded procedures (or functions) like this APEX_MAIL.SEND:

declare
var_addressee varchar2(255) := '...';
var_sender    varchar2(255) := '...';
var_title     varchar2(80);
begin
apex_mail.send(
p_to   => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => null);
end;

ORA-06550: line 6, column 1:
PLS-00307: too many declarations of 'SEND' match this call
ORA-06550: line 6, column 1: PL/SQL: Statement ignored

The problem is highlighted in purple.
Since APEX_MAIL.SEND comes in two versions (in other words it is an overloaded procedure), if we pass null to parameter p_body, the parser cannot decide whether it should reference the function version accepting p_body as VARCHAR2 or the version accepting p_body as CLOB.

The workaround is to replace null with some more meaningful expression.
Note that in general it could be enough to store null into a variable of the desired type, but this is not the case of APEX_MAIL.SEND that would just return the error i described in my previous posting.

ORA-06502 with APEX_MAIL.SEND

Just a quick comment about an elusive error message that you may get when using a built-in procedure for sending emails from Oracle Application Express (APEX).

I am referring to APEX_MAIL.SEND, Apex version 3.0, but probably it affects also the previous versions.

I have a simple procedure call like:

begin
...
apex_mail.send(
p_to => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => var_message);
...
end;

If the content of var_message is null, you'll get this fairly generic error:

ORA-06502: PL/SQL: numeric or value error

If you are building the message body dynamically, you must ensure that the value passed to the parameter p_body is not null, using function NVL perhaps, for delivering a message in pure Magritte style as follows:

begin
...
apex_mail.send(
p_to   => var_addressee,
p_from => var_sender,
p_subj => var_title,
p_body => nvl(var_message,'message body is empty')
);
...
end;

Or any other message that you like best.