How Do I Configure Email in Oracle APEX

 

Introduction
Configuring email in Oracle APEX allows your applications to send messages such as user notifications, form confirmations, workflow alerts, and system-generated updates. Before sending emails from your APEX apps, you must set up the appropriate SMTP server details and ensure the APEX environment is properly configured. A correct email setup is essential for automating communication and enhancing user engagement.

 Before using email functionality, the APEX instance must be configured to send emails. This is typically done by setting up an SMTP (Simple Mail Transfer Protocol) server.

• SMTP Host Address: The outgoing mail server that Oracle APEX uses to send emails.

• SMTP Authentication: If required, provide credentials to authenticate the email sender.

• SMTP Port: The port number used to connect to the mail server, typically 25, 465 (SSL), or 587 (TLS).

• Use Secure Connection: Determines if SSL/TLS encryption is used for email communication. 

To configure email in Oracle APEX, you must set up the Simple Mail Transfer Protocol (SMTP) settings that APEX uses to send emails through the APEX_MAIL package. This configuration enables your application to deliver confirmation messages, workflow notifications, or alerts directly to user inboxes. The setup involves both infrastructure-level configuration and application-level PL/SQL logic.

Here is a step-by-step guide on how to configure email in Oracle APEX:

1. Set Up SMTP Configuration (Instance Level or ORDS)
   Depending on where Oracle APEX is deployed (on-premise, Oracle Cloud, or autonomous database), SMTP settings must be defined:

   • For Oracle Cloud / APEX Service, use the Manage Instance section in APEX Admin:

     • Go to Manage Instance > Instance Settings > Email.

     • Enter:

       • SMTP Host (e.g., smtp.office365.com)

       • Port (e.g., 587 for TLS)

       • Username (e.g., noreply@yourdomain.com)

       • Password (application password or mail server password)

       • SSL/TLS as required by the mail server

   • For On-Premise Using ORDS, edit the ords.defaults.properties file or equivalent configuration file:

     mail.smtp.host=smtp.yourdomain.com
     mail.smtp.port=587
     mail.smtp.username=noreply@yourdomain.com
     mail.smtp.password=yourpassword
     mail.smtp.auth=true
     mail.smtp.starttls.enable=true
     

2. Verify SMTP Settings
   After saving the settings, restart ORDS (if required) and test by sending a simple email using PL/SQL. If using a cloud setup, the test can be done directly from the APEX interface.

3. Grant Required Privileges
   Grant the EXECUTE privilege on the APEX_MAIL package to your application schema if it's not already granted:

   GRANT EXECUTE ON APEX_MAIL TO your_schema;
   

4. Create a Simple PL/SQL Process to Send Email
   Use APEX_MAIL.SEND and APEX_MAIL.PUSH_QUEUE in a PL/SQL block. For example:

   BEGIN
     APEX_MAIL.SEND(
       p_to        => 'user@example.com',
       p_from      => 'noreply@yourdomain.com',
       p_subj      => 'Thank you for your submission',
       p_body      => 'We received your form successfully.',
       p_body_html => '<p>We received your <strong>form</strong> successfully.</p>'
     );
   
     APEX_MAIL.PUSH_QUEUE;
   END;
   

5. Understand the Mail Queue Behavior
   Oracle APEX queues outgoing emails in a table (APEX_MAIL_QUEUE). Emails are not sent until either:

   • APEX_MAIL.PUSH_QUEUE is called, or

   • The automated mail queue job runs (if scheduled using DBMS_SCHEDULER or the ORDS environment supports automatic pushing)

6. Monitor and Debug Email Status

   • Check APEX_MAIL_LOG for delivery status and errors

   • Check APEX_MAIL_QUEUE for pending messages

   • Example:

     SELECT * FROM APEX_MAIL_LOG ORDER BY SENT_DATE DESC;
     

7. Tips for Success

   • Always use a verified domain that has proper SPF and DKIM records to prevent messages from being flagged as spam.

   • If testing with Gmail, Outlook, or Office365, use app-specific passwords or OAuth-based mail accounts.

   • Make sure firewalls or network security rules allow SMTP traffic from the APEX server.

8. Use HTML and Text Bodies Together
   To ensure compatibility with all email clients, always send both p_body (plain text) and p_body_html (HTML formatted message).

9. Use Substitution Strings for Dynamic Content
   Customize email content using page items, application items, or SQL query results:

   APEX_MAIL.SEND(
     p_to        => :P1_EMAIL,
     p_from      => 'noreply@yourdomain.com',
     p_subj      => 'Hello ' || :P1_NAME,
     p_body      => 'Dear ' || :P1_NAME || ', your request has been received.',
     p_body_html => '<p>Dear ' || :P1_NAME || ',<br>Your request has been received.</p>'
   );
   

By following these steps, you configure Oracle APEX to send emails using the APEX_MAIL package and SMTP settings. With correct setup and permissions, your APEX applications can automate communications and improve user interaction through reliable email delivery.

Conclusion
By successfully configuring email in Oracle APEX, you unlock the ability to send real-time, automated messages that improve the functionality and responsiveness of your application. With the right SMTP settings in place and proper use of the APEX_MAIL package, your app can deliver critical information to users efficiently and reliably.