APEX_MAIL.SEND function not working though its not giving any error

Question

Have to send email from oracle apex using APEX_MAIL.SEND() method.

I am using the code:

BEGIN

 apex_mail.send(p_to   => '[email protected]'/*l_to_addr*/,        
                 p_from => '[email protected]'/*l_from_addr*/,
                 p_bcc  => l_bcc_addr,
                 p_subj => l_mail_sub,
                 p_body => 'Service Request ' || :mail_body ||
'Note:- This is a system generated Email. Please DO NOT REPLY to it.');

apex_mail.push_queue;  

EXCEPTION

  when others then    
       INSERT INTO send_mail_error_test VALUES ('Send_mail',systimestamp,:service_request_id||'-err:'||seq_service_req_error_id.NEXTVAL);  

COMMIT;  

END;

*** all the variables contain correct values

1. Working with a database and that database has ACL(access control list) access

2. In apex administrative services, Configured instance settings for email as hostname, port , email provisioning enabled.

3. UTL_SMTP package is installed

4. From the same process, at the same point of control flow a code to send mail using utl_Smtp is working fine, though that apex_mail.send() is not working.

5. That apex_mail.send() is not giving any error or exception, but i am not recieving email from it.

6. Have one confusion, got some solutions like that, 'APEX_040200' should have been added to ACL. But the database I am using and implementing the code on it, say 'apex_user' is already added to ACL. Even now do I have to add 'APEX_040200' or 'APEX_050200' to ACL?

can anyone help me out and give me a fruitful solution? I am using apex 5.0.2.00.07.

Answer 1

As can be found in the apex_mail api documentation:

Before you can send email from an Application Builder application, you must:

1. Log in to Oracle Application Express Administration Services and configure the email settings on the Instance Settings page. See "Configuring Email" in Oracle Application Express Administration Guide.

2. If you are running Oracle Application Express with Oracle Database 11g release 1 (11.1), you must enable outbound mail. In Oracle Database 11g release 1 (11.1), the ability to interact with network services is disabled by default. See "Enabling Network Services in Oracle Database 11g" in Oracle Application Express Application Builder User's Guide.

You specified instance settings are ok. Your "database has ALC access" doesn't mean anything. Do you mean you have a database which uses network ACLs? (11g or higher) That same documentation links to "the Enabling Network Services in Oracle Database 11g or Later" documentation

This document does not leave you guessing:

By default, the ability to interact with network services is disabled in Oracle Database 11g Release 1 or 2 or later. Therefore, if you are running Oracle Application Express with Oracle Database 11g Release 1 or 2 or later, you must use the new DBMS_NETWORK_ACL_ADMIN package to grant connect privileges to any host for the APEX_050000 database user. Failing to grant these privileges results in issues with:...

If you have an older version of apex, eg 4.2, the user to grant to is another one, and can be found in the documentation. Alternatively, you can just find out by for example querying the ALL_USERS view and find the APEX_###### users, pick the one with the highest version number:

select *
from all_users
where username like 'APEX%'
order by username;

Answer 2

in our case, it was a job, ORACLE_APEX_MAIL_QUEUE, which had status 'RUNNING' for 8 days. Apparently, it held some kind of lock on the queue or mailprocess

We killed the job and that was it.

(see dba_scheduler_jobs for the job & status)