APEX Mail Server Setup for Reset Password Functionality

Oracle APEX requires a properly configured Mail Server (SMTP) to send emails, including password reset links. Below is a step-by-step guide to configure APEX Mail Server and enable password reset emails.

1. Verify APEX Mail Configuration

First, check if your APEX environment has email sending enabled.

Run the following query in SQL Developer or SQL*Plus:


SELECT * FROM APEX_INSTANCE_PARAMETERS 
WHERE parameter_name = 'SMTP_HOST_ADDRESS';

If this returns no value, then SMTP is not configured.

2. Configure SMTP in APEX

Set up the SMTP server details using the following command:


BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER(
        p_parameter => 'SMTP_HOST_ADDRESS',
        p_value     => 'smtp.yourdomain.com'-- Replace with your mail server
    );
    COMMIT;
END;
/

If authentication is required, configure the SMTP username and password:


BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_USERNAME', 'your_email@yourdomain.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_PASSWORD', 'your_password');
    COMMIT;
END;
/

Optionally, set the SMTP port (default is 25, but some servers use 587 for TLS or 465 for SSL):


BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_PORT', '587'); -- Adjust as needed
    APEX_INSTANCE_ADMIN.SET_PARAMETER
('SMTP_TLS_MODE', 'REQUIRED'); -- Use TLS if needed
    COMMIT;
END;
/

Network ACL
We need to make sure the the database can make a callout to the mail server. 
This requires a network ACL for the specific host and port. 
In the following example we are using "localhost:25", a local relay on the database server. 
The principal of the ACL must the the "APEX_XXXXXX" user.

declare
  l_username varchar2(30) := 'APEX_220100';
begin
  dbms_network_acl_admin.append_host_ace(
    host => 'localhost',
    lower_port => 25,
    ace  =>  xs$ace_type(privilege_list => xs$name_list('connect'),
                         principal_name => l_username,
                         principal_type => xs_acl.ptype_db));
  commit;
end;
/

Validate the Email

SET SERVEROUTPUT ON;
BEGIN
    APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG;
END;
/

3. Test Email Sending in APEX

To confirm that emails can be sent, run:


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 for email processing errors:


SELECT * FROM APEX_MAIL_QUEUE WHERE MAIL_SEND_ERROR IS NOT NULL;

If emails are stuck, run:


BEGIN
    APEX_MAIL.PUSH_QUEUE;
END;
/

4. Enable Reset Password Functionality

To allow password reset emails, ensure that APEX authentication schemes are set correctly:

Navigate to APEX Application Builder.
Go to Shared Components > Authentication Schemes.
Edit your authentication scheme (e.g., APEX Accounts or Custom).
Under Lost Password settings, enable Reset Password.
Set Reset Password Link Expiration (e.g., 24 hours).
Save changes.

5. Final Testing

Request a Password Reset
- Go to the APEX login page.
- Click "Forgot Password?".
- Enter your email.
- Check if the email is received.

Check APEX Mail Logs
If no email is received, check the logs:


SELECT * FROM APEX_MAIL_LOG ORDER BY SENT_DATE DESC;

Troubleshooting

Check SMTP Connectivity:
```
telnet smtp.yourdomain.com 25
```
If the connection fails, verify firewall rules.
Check SMTP Authentication & Ports:
Ensure the mail server allows relay and authentication.
Check APEX Mail Queue Processing:
```
SELECT * FROM APEX_MAIL_QUEUE;
```

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

APEX Mail Server Setup for Reset Password Functionality