How to Send Emails from Oracle APEX with APEX_MAIL (SMTP, Attachments & Templates)

Almost every business application eventually needs to send email: approval notifications, password links, invoices, daily summaries or alerts when something fails. Oracle APEX includes the APEX_MAIL package for exactly this purpose. It is reliable once configured, but most problems teams hit are not in the PL/SQL call itself — they are in SMTP settings, database network permissions and TLS certificates.

This guide walks through the complete setup in the order you should do it, followed by working code examples and a troubleshooting section for the errors we see most often in real projects.

1. Understand how APEX_MAIL works

APEX_MAIL.SEND does not connect to the mail server immediately. It writes the message to the APEX mail queue. A background job then pushes queued messages to your SMTP server, usually within a few minutes. This design keeps page processing fast, but it also means a successful SEND call does not prove the email was delivered. You check delivery status in the mail queue and mail log views described later.

2. Configure the SMTP server in APEX

An instance administrator sets the SMTP details in APEX Administration Services → Manage Instance → Instance Settings → Email. The key values are:

• SMTP Host Address – for example your Microsoft 365, Gmail Workspace, SendGrid or company mail relay.
• SMTP Host Port – commonly 587 (STARTTLS) or 465 (implicit TLS); 25 is usually blocked by hosting providers.
• Use SSL/TLS – choose the mode your provider requires.
• SMTP Authentication Username and Password – often an app password or API credential rather than a normal user password.
• Default Email From Address – use an address your SMTP provider is allowed to send from, otherwise messages are rejected or marked as spam.

On Oracle Autonomous Database, the equivalent settings are made with APEX_INSTANCE_ADMIN.SET_PARAMETER (for example SMTP_HOST_ADDRESS, SMTP_USERNAME and SMTP_PASSWORD) and the Email Delivery service credentials.

3. Grant the network ACL

From Oracle Database 11g onward, PL/SQL cannot open network connections unless an Access Control List allows it. The APEX engine schema (named after the APEX version, such as APEX_240200) needs permission to connect to your SMTP host and port. Run this as a privileged user, replacing the host, port and schema name:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host       => 'smtp.yourprovider.com',
    lower_port => 587,
    upper_port => 587,
    ace        => xs$ace_type(
                    privilege_list => xs$name_list('connect'),
                    principal_name => 'APEX_240200',
                    principal_type => xs_acl.ptype_db));
END;
/

You can find the correct schema name with SELECT username FROM dba_users WHERE username LIKE 'APEX_2%'; — use the highest version number currently installed.

4. Add a wallet for TLS connections

If your SMTP server uses TLS, the database must trust the server’s certificate chain. Create an Oracle wallet containing the provider’s root and intermediate certificates, then set the wallet path and password in APEX instance settings (Instance Settings → Wallet). Skipping this step is the most common cause of ORA-29024: Certificate validation failure.

5. Send a simple email

Once configuration is complete, sending a message from a page process or package is straightforward:

BEGIN
  APEX_MAIL.SEND(
    p_to   => 'customer@example.com',
    p_from => 'no-reply@yourcompany.com',
    p_subj => 'Your request has been approved',
    p_body => 'Hello, your request #1045 has been approved.');
END;

6. Send an HTML email

Provide both a plain-text body and an HTML body. Email clients that cannot display HTML fall back to the text version, and spam filters treat messages with both parts more favourably.

APEX_MAIL.SEND(
  p_to        => :P10_EMAIL,
  p_from      => 'no-reply@yourcompany.com',
  p_subj      => 'Invoice ' || :P10_INVOICE_NO,
  p_body      => 'Please find your invoice details below.',
  p_body_html => '<p>Dear ' || APEX_ESCAPE.HTML(:P10_NAME) || ',</p>'
              || '<p>Your invoice <b>' || APEX_ESCAPE.HTML(:P10_INVOICE_NO)
              || '</b> is ready.</p>');

Always escape user-supplied values with APEX_ESCAPE.HTML before placing them inside HTML email content.

7. Add attachments

APEX_MAIL.SEND is also available as a function that returns the mail ID. Use that ID to attach one or more BLOBs, such as a generated PDF invoice:

DECLARE
  l_mail_id NUMBER;
  l_pdf     BLOB;
BEGIN
  SELECT file_blob INTO l_pdf
    FROM invoice_files
   WHERE invoice_id = :P10_INVOICE_ID;

  l_mail_id := APEX_MAIL.SEND(
                 p_to   => :P10_EMAIL,
                 p_from => 'accounts@yourcompany.com',
                 p_subj => 'Invoice ' || :P10_INVOICE_NO,
                 p_body => 'Your invoice is attached.');

  APEX_MAIL.ADD_ATTACHMENT(
    p_mail_id    => l_mail_id,
    p_attachment => l_pdf,
    p_filename   => 'invoice_' || :P10_INVOICE_NO || '.pdf',
    p_mime_type  => 'application/pdf');
END;

8. Use email templates for consistent messages

Instead of building HTML strings in PL/SQL, define templates in Shared Components → Email Templates with placeholders such as #CUSTOMER_NAME# and #ORDER_NO#. Then send with:

APEX_MAIL.SEND(
  p_template_static_id => 'ORDER_CONFIRMATION',
  p_placeholders       => '{"CUSTOMER_NAME":"' || APEX_ESCAPE.JSON(:P5_NAME) || '",'
                       || '"ORDER_NO":"' || :P5_ORDER_NO || '"}',
  p_to                 => :P5_EMAIL);

Templates let business users review wording and layout without touching code, and every email follows the same branding.

9. Sending email outside an APEX session

When APEX_MAIL is called from a scheduler job, a database trigger or SQL*Plus, there is no APEX session to tell it which workspace to use. Set the workspace first:

BEGIN
  APEX_UTIL.SET_WORKSPACE(p_workspace => 'MY_WORKSPACE');
  APEX_MAIL.SEND(p_to => 'ops@yourcompany.com',
                 p_from => 'no-reply@yourcompany.com',
                 p_subj => 'Nightly job finished',
                 p_body => 'The nightly stock reconciliation completed successfully.');
  APEX_MAIL.PUSH_QUEUE;
  COMMIT;
END;

PUSH_QUEUE sends queued messages immediately instead of waiting for the background job. Note that queued mail is only sent after the transaction commits.

10. Monitor the queue and log

Two views tell you what actually happened:

• APEX_MAIL_QUEUE – messages waiting to be sent, including the last error for messages that failed.
• APEX_MAIL_LOG – messages that were handed to the SMTP server successfully.

Administrators can also view and resend queued messages in Administration Services → Manage Instance → Mail Queue.

11. Troubleshooting common errors

• ORA-24247: network access denied by access control list – the ACL in step 3 is missing, is for the wrong APEX schema, or does not cover the port.
• ORA-29024: Certificate validation failure – the wallet is missing the provider’s certificates or the wallet path is wrong.
• ORA-29278 / 530 authentication errors – incorrect SMTP credentials, or the provider requires an app password.
• Emails stuck in the queue – check the error column in APEX_MAIL_QUEUE and confirm the transaction was committed.
• Emails delivered to spam – make sure the From address belongs to a domain with correct SPF, DKIM and DMARC records for your SMTP provider.

Quick checklist

• SMTP host, port, TLS mode and credentials configured
• Network ACL granted to the current APEX schema
• Wallet configured for TLS
• From address authorised by the SMTP provider
• User values escaped in HTML emails
• Workspace set for jobs running outside APEX
• Queue and log views monitored after go-live

Need help with Oracle APEX email or notifications?

AbairTech builds Oracle APEX applications with approval workflows, scheduled reports and email notifications. See Oracle APEX Development Services or contact us to discuss your requirement.