Viewing APEX Mail Log & Queue in Oracle APEX

Oracle APEX provides built-in email functionality using the APEX_MAIL package. When emails are sent, they are queued for processing. To troubleshoot email delivery issues, you can check the APEX Mail Log and Queue.

This guide walks you through viewing, analyzing, and troubleshooting APEX emails step by step.

1. Understanding APEX Mail Processing

How APEX Handles Emails

Emails are queued in the APEX_MAIL_QUEUE table.
The background job APEX_MAIL.PUSH_QUEUE processes emails.
Email status and logs are stored in APEX_MAIL_LOG.

Common Issues

Emails stuck in the queue.
SMTP server misconfiguration.
Authentication failures.
Network restrictions (firewall or SMTP block).

2. Checking the APEX Mail Queue

To view pending emails in the queue, run:


SELECT MAIL_ID, SENT_DATE, SENT_STATUS, MAIL_SEND_ERROR, TO_EMAIL 
FROM APEX_MAIL_QUEUE
ORDER BY SENT_DATE DESC;

Column Details:

MAIL_ID: Unique email identifier.
SENT_DATE: Timestamp when the email was sent.
SENT_STATUS: Email status (SENT, FAILED, QUEUED).
MAIL_SEND_ERROR: Error message if sending failed.
TO_EMAIL: Recipient email address.

3. Checking the APEX Mail Log

To view sent email logs, run:


SELECT LOG_ID, MAIL_ID, SENT_DATE, SENT_STATUS, 
MAIL_SEND_ERROR, TO_EMAIL
FROM APEX_MAIL_LOG
ORDER BY SENT_DATE DESC;

Interpreting Results:

If SENT_STATUS = SENT → Email was successfully delivered.
If SENT_STATUS = FAILED, check MAIL_SEND_ERROR for issues.
If SENT_STATUS = QUEUED, email is waiting to be processed.

4. Forcing APEX to Process the Mail Queue

If emails are stuck in the queue, manually push them:


BEGIN
    APEX_MAIL.PUSH_QUEUE;
END;
/

This forces APEX to process all pending emails in the queue.

5. Checking SMTP Configuration

Verify the SMTP server settings:


SELECT * FROM APEX_INSTANCE_PARAMETERS 
WHERE PARAMETER_NAME LIKE 'SMTP%';

Common settings to check:

SMTP_HOST_ADDRESS: SMTP server hostname.
SMTP_USERNAME: Email username.
SMTP_PASSWORD: Encrypted SMTP password.
SMTP_PORT: SMTP port (typically 25, 465, or 587).
SMTP_TLS_MODE: TLS encryption mode (REQUIRED, OPTIONAL).

If these values are missing, configure SMTP using:


BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_HOST_ADDRESS', 'smtp.yourdomain.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_USERNAME', 'your_email@yourdomain.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_PASSWORD', 'your_password');
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_PORT', '587');
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_TLS_MODE', 'REQUIRED');
    COMMIT;
END;
/

6. Testing Email Sending in APEX

Send a test email using:


BEGIN
    APEX_MAIL.SEND(
        p_to       => 'test@yourdomain.com',
        p_from     => 'noreply@yourdomain.com',
        p_subj     => 'Test Email from APEX',
        p_body     => 'This is a test email from Oracle APEX.'
    );
    COMMIT;
END;
/

Check the queue:


SELECT * FROM APEX_MAIL_QUEUE ORDER BY SENT_DATE DESC;

Force email processing:


BEGIN
    APEX_MAIL.PUSH_QUEUE;
END;
/

7. Troubleshooting Common Issues

1️⃣ Emails Stuck in Queue

Check SMTP settings using:


SELECT * FROM APEX_INSTANCE_PARAMETERS 
WHERE PARAMETER_NAME LIKE 'SMTP%';

Ensure the APEX_MAIL.PUSH_QUEUE process runs.

2️⃣ SMTP Authentication Errors

Check MAIL_SEND_ERROR in APEX_MAIL_QUEUE.
Verify SMTP credentials are correct.
Confirm SMTP authentication is enabled on the server.

3️⃣ Firewall Blocking Emails

Check network rules allowing outbound connections to SMTP.
Run a telnet test:
```
telnet smtp.yourdomain.com 587
```
If this fails, contact the network team.

4️⃣ Emails Marked as Spam

Use a verified domain with SPF, DKIM, and DMARC records.
Send emails from a recognized sender domain.

8. Automating Email Processing

If emails don’t send automatically, check and enable the APEX Mail Queue Job:


SELECT JOB_NAME, STATE FROM 
DBA_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'APEX_MAIL_QUEUE%';

If the job is disabled, re-enable it:


BEGIN
    DBMS_SCHEDULER.ENABLE('APEX_MAIL_QUEUE');
END;
/

9. Final Verification

Check SMTP Configuration


SELECT * FROM APEX_INSTANCE_PARAMETERS WHERE PARAMETER_NAME LIKE 'SMTP%';

Test Email Sending


BEGIN
    APEX_MAIL.SEND
(p_to => 'test@yourdomain.com', 
p_from => 'noreply@yourdomain.com', 
p_subj => 'Test', p_body => 'Test Email');
    COMMIT;
END;
/

Check Email Queue & Logs


SELECT * FROM APEX_MAIL_QUEUE ORDER BY SENT_DATE DESC;
SELECT * FROM APEX_MAIL_LOG ORDER BY SENT_DATE DESC;

Process Mail Queue Manually


BEGIN
    APEX_MAIL.PUSH_QUEUE;
END;
/

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Viewing APEX Mail Log & Queue in Oracle APEX