How To Use Email Delivery Works in Oracle APEX

 

Introduction
Email delivery in Oracle APEX allows your applications to send notifications, confirmations, alerts, and reports directly to users’ inboxes. Whether triggered by form submissions, scheduled processes, or workflow events, integrating email functionality enhances communication and automates important interactions. Understanding how APEX handles email—from configuration to sending—is essential for creating responsive, user-friendly applications.

 

1. User or System Triggers an Email

• A user action, a scheduled job, or a system event calls the APEX_MAIL.SEND procedure to send an email.

2. Email is Placed in the APEX Mail Queue

• APEX does not send emails immediately. Instead, they are added to a queue and processed asynchronously.

3. Oracle APEX Processes the Queue

• The APEX_MAIL.PUSH_QUEUE procedure sends the queued emails via the configured SMTP server.

4. SMTP Server Delivers the Email

• The SMTP server authenticates and delivers the email to the recipient's email provider.

5. Recipient Receives the Email

• The recipient’s email provider processes the email and delivers

Using email delivery in Oracle APEX enables applications to send emails to users for notifications, alerts, confirmations, and workflow-related messages. This functionality is commonly used for contact forms, approval processes, password resets, and scheduled reports. Oracle APEX provides built-in support through the APEX_MAIL package, which works with your configured SMTP server to send email messages from your application.

Here is a detailed explanation of how email delivery works in Oracle APEX:

1. Configure SMTP Settings
   Before sending emails, you must configure the SMTP server details for your APEX instance.
   If you are using Oracle APEX on Oracle Cloud or on an on-premise Oracle REST Data Services (ORDS) setup, the SMTP settings must be configured in the instance settings or at the Web Listener level.

   For ORDS-managed environments:

   • Edit the ords.war or configuration files to set the SMTP host, port, username, password, and secure connection options (TLS/SSL).

   • You can also use Oracle APEX Instance Administration to enter the SMTP server and port in Manage Instance > Instance Settings > Email.

2. Grant Access to APEX_MAIL
   Ensure that the schema owning your application has the necessary privileges:

   GRANT EXECUTE ON APEX_MAIL TO your_schema;
   

3. Compose and Send an Email Using APEX_MAIL
   You can use the APEX_MAIL.SEND procedure in a PL/SQL process (e.g., on page submit or after insert). Here's an example:

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

   • p_to: recipient email address

   • p_from: sender’s email (must match the SMTP sender policy)

   • p_subj: subject line

   • p_body: plain text body

   • p_body_html: HTML version (optional but recommended for rich formatting)

4. Push the Mail Queue
   Emails are queued in the APEX_MAIL_QUEUE table and not sent immediately unless you explicitly call:

   APEX_MAIL.PUSH_QUEUE;
   

   Alternatively, the background job that pushes mail from the queue can be configured to run periodically using DBMS_SCHEDULER or ORDS.

5. Check the Mail Queue and Logs
   You can monitor the email status from these views:

   • APEX_MAIL_LOG – for success/failure status of sent emails

   • APEX_MAIL_QUEUE – for pending emails waiting to be sent

   Example:

   SELECT * FROM APEX_MAIL_LOG ORDER BY SENT_DATE DESC;
   

6. Use Bind Variables and Dynamic Content
   You can personalize the email content by embedding data from application items or table rows. Use v() to refer to items or construct messages dynamically within PL/SQL.

   Example:

   DECLARE
     l_message VARCHAR2(4000);
   BEGIN
     l_message := 'Dear ' || :P1_NAME || ', thank you for registering.';
     APEX_MAIL.SEND(
       p_to        => :P1_EMAIL,
       p_from      => 'noreply@yourdomain.com',
       p_subj      => 'Welcome!',
       p_body      => l_message
     );
     APEX_MAIL.PUSH_QUEUE;
   END;
   

7. Security and Best Practices

   • Ensure you do not expose SMTP credentials.

   • Validate and sanitize email inputs to prevent misuse.

   • Use a domain and email sender that matches your organization’s SPF/DKIM setup to prevent messages from being flagged as spam.

   • Use p_body_html for rich emails and always include p_body as a fallback.

By properly configuring your SMTP settings and using APEX_MAIL, you can build automated email workflows directly into your APEX applications. Whether you are sending alerts, approval notices, or customer confirmations, this functionality brings real-time communication to your users and supports a modern, interactive application experience.

Conclusion
Using email delivery in Oracle APEX adds powerful communication features to your application. By configuring SMTP settings, building dynamic content, and leveraging PL/SQL APIs like APEX_MAIL, you can automate messaging with precision and reliability. This ensures that users stay informed, engaged, and connected to the application’s processes in real time.