Wednesday 23 November 2011

APEX e-mail lag

When you submit e-mail as a process in Oracle Application Express, or using the Apex_Mail API in your own PL/SQL, you are essentially first submitting it to a queue - it won't be e-mailed immediately.

I have seen this minor fact trip a few people when wondering where/why their mail hasn't been delivered.

Prior to APEX 4.x, you would need to run the following SQL to find out details about the background job that manages the Apex mail queue:
select next_date, broken, what
from dba_jobs
where what like 'wwv_flow_mail%';
Apex 4.x brings us into 21st Century Oracle by using the scheduler module instead:
select job_action, next_run_date, last_run_duration, state
from dba_scheduler_jobs
where job_name = 'ORACLE_APEX_MAIL_QUEUE';
So this also means you have a number of options to push the mail queue. You can use the API mentioned ealier; call dbms_scheduler.run_job; or log in to the INTERNAL workspace and Manage Instance -> Mail Queue -> "Send all mail"

You might also be interested in the following Apex dictionary views:
APEX_MAIL_LOG
APEX_MAIL_QUEUE

And if you're looking at setting up an email server on your swiss laptop (the laptop that does everything - yes, I just invented that term), perhaps start here.

That is all,

Scott

2 comments:

Peter said...

as far as i know you can force the delivering of queued mails by calling the APEX_MAIL.PUSH_QUEUE procedure from the application

http://docs.oracle.com/cd/E23903_01/doc/doc.41/e21676/apex_mail.htm#CHDJGGHG

Scott Wesley said...

That's correct, Peter.

This is the module executed by the scheduled jobs, and what I was eluding to when I said you could "use the API mentioned earlier"

I wasn't clear, good point.