How Do I Configure SMTP Parameters in Autonomous Database.

Introduction
Configuring SMTP parameters in an Autonomous Database is essential for enabling your Oracle APEX applications to send emails such as notifications, alerts, and confirmations. Proper SMTP setup ensures that emails are sent securely and reliably through a trusted mail server. This configuration involves specifying the SMTP host, port, authentication credentials, and security protocols to connect your Autonomous Database environment with an SMTP service.

 To configure SMTP parameters in an Autonomous Database (ADB) for Oracle APEX email delivery, you must set the mail server settings that APEX uses to send outgoing emails. This setup ensures that your APEX applications can send notifications, confirmations, and alerts via email using a trusted SMTP service such as Oracle Cloud Infrastructure (OCI) Email Delivery or any external SMTP provider.

Here is a detailed step-by-step guide to configure SMTP parameters in Autonomous Database:

1. Access Oracle APEX Instance Administration
   Log in to the APEX workspace with an administrative account (usually the workspace with admin privileges or the instance admin workspace). From the APEX home page, navigate to Manage Instance or Instance Administration.

2. Open Instance Settings for Email Configuration
   In the administration interface, select Instance Settings. Scroll to the Email section where SMTP configuration parameters are set.

3. Set SMTP Host and Port
   Enter the hostname or IP address of your SMTP server in the SMTP Host Address field. For OCI Email Delivery, this might be a region-specific endpoint such as:
   smtp.email.us-ashburn-1.oci.oraclecloud.com

   Specify the SMTP port:

   • Use 587 for TLS/STARTTLS

   • Use 465 for SSL connections

4. Enter SMTP Authentication Credentials
   Provide the SMTP username and password in the respective fields. For OCI Email Delivery, this will be the SMTP credentials generated in the Oracle Cloud Console under Identity > Users > SMTP Credentials.

5. Enable Secure Connection
   Select whether to use SSL or TLS/STARTTLS based on your SMTP server requirements. For most modern SMTP services including OCI Email Delivery, enabling STARTTLS on port 587 is recommended to encrypt the connection.

6. Configure Default Sender Email
   Enter a valid and approved sender email address. This is the email address that will appear in the From field of outgoing emails. For OCI Email Delivery, this address must be registered as an approved sender in the OCI Email Delivery service.

7. Save and Apply Settings
   After entering all the required SMTP parameters, save the configuration. The changes apply immediately to outgoing emails sent from APEX applications in this Autonomous Database environment.

8. Test Email Configuration
   You can verify the configuration by sending a test email from the Instance Administration page or by executing a PL/SQL block using the APEX_MAIL package, such as:

   BEGIN
     APEX_MAIL.SEND(
       p_to        => 'your.email@example.com',
       p_from      => 'noreply@yourdomain.com',
       p_subj      => 'SMTP Configuration Test',
       p_body      => 'This is a test email sent after configuring SMTP parameters in Autonomous Database.'
     );
     APEX_MAIL.PUSH_QUEUE;
   END;
   

9. Monitor Mail Logs and Queue
   Check the status of sent emails by querying the APEX_MAIL_LOG and APEX_MAIL_QUEUE views to troubleshoot delivery or queue issues.

10. Best Practices

    • Use secure SMTP authentication to protect credentials.

    • Keep your SMTP credentials confidential and never expose them in public code repositories.

    • Ensure your domain’s SPF and DKIM DNS records authorize the SMTP server to send emails on behalf of your domain to prevent delivery issues.

    • Regularly monitor email logs to identify any sending failures.

By carefully configuring the SMTP parameters in your Autonomous Database instance, you enable your Oracle APEX applications to reliably send emails, improving communication and workflow automation for end users.

• Connect to the Autonomous Database as an ADMIN user.

• Use the APEX_INSTANCE_ADMIN.SET_PARAMETER procedure to set SMTP parameters.

• Example PL/SQL script:

BEGIN

    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.us-phoenix-1.oraclecloud.com');

    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1.username');

    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'password');

    COMMIT;

END;

/

• The default values for SMTP_HOST_PORT (587) and SMTP_TLS_MODE (STARTTLS) should be retained.

Setting Up SMTP in Oracle APEX

The SMTP settings can be configured at the Instance Level in Oracle APEX. Follow these steps to configure SMTP settings:

1. Log in to APEX Instance Administration.

2. Navigate to Email Configuration under the Instance Settings.

3. Enter the SMTP Host, Port, and any required authentication details.

Save the configuration and test email functionality using APEX_MAIL.SEND.

Using APEX_MAIL to Send Emails

Oracle APEX provides the APEX_MAIL package to send emails programmatically from within an application.

Example of sending an email using PL/SQL:

BEGIN

    APEX_MAIL.SEND (

        p_to        => 'recipient@example.com',

        p_from      => 'sender@example.com',

        p_subj      => 'Test Email from APEX',

        p_body      => 'This is a test email sent from Oracle APEX.'

    );

    COMMIT; -- Required to process the email queue

END;

• p_to: Recipient’s email address.

• p_from: Sender’s email address (must be configured in SMTP settings).

• p_subj: Email subject line.

• p_body: Email message content.

How To Programatically Send HTML Emails

To format emails with HTML content, use the p_body_html parameter:

BEGIN

    APEX_MAIL.SEND (

        p_to        => 'recipient@example.com',

        p_from      => 'sender@example.com',

        p_subj      => 'HTML Email from APEX',

        p_body_html => '<h1>Hello from APEX</h1><p>This is a test email.</p>'

    );

    COMMIT;

END;

How To Programatically Send Emails with Attachments

DECLARE

    v_attachment BLOB;

BEGIN

    -- Retrieve the file from APEX collection, table, or another source

    SELECT file_blob INTO v_attachment FROM my_table WHERE file_id = 1;

    

    APEX_MAIL.ADD_ATTACHMENT (

        p_mail_id     => APEX_MAIL.SEND (

            p_to    => 'recipient@example.com',

            p_from  => 'approved-sender@example.com',

            p_subj  => 'Email with Attachment',

            p_body  => 'Please find the attached file.'

        ),

        p_attachment  => v_attachment,

        p_filename    => 'document.pdf',

        p_mime_type   => 'application/pdf'

    );

    COMMIT;

END;

Manually/ Programatically Processing the Email Queue

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

BEGIN

    APEX_MAIL.PUSH_QUEUE;

END;

 

 Debugging Email Issues

If emails are not being sent, check the following:

• Verify SMTP settings in Instance Administration.

• Ensure the APEX_MAIL_QUEUE has no errors.

• Check the database logs for any SMTP connection errors.

Make sure the APEX Mail Queue Process is scheduled and running.

 

Conclusion
By correctly configuring SMTP parameters in your Autonomous Database, you empower your Oracle APEX applications to communicate effectively with users via email. This setup not only supports essential messaging features but also helps maintain security and deliverability, ensuring that your application’s emails reach their intended recipients consistently and securely.