My Oracle Support Banner

Outbound Email Delivery From APEX Seems To Stall (Doc ID 2057872.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Application Express (APEX) - Version 4.2.6 and later
Information in this document applies to any platform.

Symptoms

On : 4.2.6 version, Administration

ACTUAL BEHAVIOR
---------------
Emails seems to be enqueued and not sent or sent very slowly (few at a time)


STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Send an email inside apex using code similar to:

----

PROCEDURE send_mail (
 p_workspace IN VARCHAR2
 , p_from IN VARCHAR2
 , p_to IN VARCHAR2
 , p_subject IN VARCHAR2
 , p_body IN VARCHAR2
) AS
 v_workspace_id varchar2(1000);
BEGIN
 BEGIN
   SELECT workspace_id INTO v_workspace_id FROM apex_workspaces WHERE trim(lower(workspace)) = trim(lower(p_workspace));
   EXCEPTION WHEN no_data_found THEN v_workspace_id := '#############';
 END;
  apex_util.set_security_group_id(v_workspace_id);
 
 apex_mail.send(
   p_to => p_to
   , p_from => p_from
   , p_subj => p_subject
   , p_body => p_body
 );
 apex_mail.push_queue('<SMTP_SERVER_IP>', '25');
END send_mail;

------


2. Check apex email queue

   Go to Oracle Application Express Administration Services.
   Click mail Queue under "Manage Meta Data" section Check on the very right side
.


3. See lots of messages enqueued


Other relevant information:
________________
1 Sending ONE email using below code in sql*workshop runs quickly.

----------

DECLARE
 l_mailhost VARCHAR2(64) := '<SMTP_SERVER_IP>';
 l_mail_conn UTL_SMTP.connection;
BEGIN
 l_mail_conn := UTL_SMTP.open_connection((l_mailhost), 25);
 UTL_SMTP.helo(l_mail_conn, l_mailhost);
 UTL_SMTP.mail(l_mail_conn, 'host@company.org');
 UTL_SMTP.rcpt(l_mail_conn, 'name.lastname@company.net');
 UTL_SMTP.open_data(l_mail_conn);
 UTL_SMTP.write_data(l_mail_conn, 'Subject: TEST SMTP Email from Database Server' || UTL_TCP.CRLF);
 UTL_SMTP.write_data(l_mail_conn, UTL_TCP.CRLF || 'How long will this take?' );
 UTL_SMTP.close_data(l_mail_conn);
 UTL_SMTP.quit(l_mail_conn);
END;

 

2 Is possible to send emails using localhost instead of remote SMTP IP address

3 ACL is granted to APEX (apex_040200) schema but NOT to SYS

Changes

 Nothing on Apex or Database side that user are aware of.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.