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

Last updated on FEBRUARY 07, 2017

Applies to:

Oracle Application Express (formerly HTML DB) - 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;
 wwv_flow_api.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

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 hundreds of Community platforms