Send Email from Oracle Application Express

You can use the APEX_MAIL package to send emails from Oracle Application Express applications deployed in Autonomous Data Warehouse.

Before you use APEX_MAIL you must configure an email provider in your Application Express instance. Currently, the only supported email provider is Oracle Cloud Infrastructure Email Delivery service.

To enable APEX_MAIL functionality in your Application Express instance in Autonomous Data Warehouse:

  1. Identify the SMTP connection endpoint for Email Delivery. You configure the endpoint as the SMTP Host in your Application Express instance in Step 4. You may need to subscribe to additional Oracle Cloud Infrastructure regions if Email Delivery is not available in your current region. See Configure SMTP Connection for more information.
  2. Generate SMTP credentials for Email Delivery. Your Application Express instance uses credentials to authenticate with Email Delivery servers when you send email. See Generate SMTP Credentials for a User for more information.
  3. Create an approved sender for Email Delivery. You need to complete this step for all email addresses you use as the "From" with APEX_MAIL.SEND calls, as the Application Email From Address in your apps, or in the SMTP_FROM instance parameter. See Managing Approved Senders for more information.
  4. Connect to your Autonomous Data Warehouse as ADMIN user using a SQL client and configure the following SMTP parameters using APEX_INSTANCE_ADMIN.SET_PARAMETER:
    • SMTP_HOST_ADDRESS: Specifies the SMTP connection endpoint from Step 1.
    • SMTP_USERNAME: Specifies the SMTP credential user name from Step 2.
    • SMTP_PASSWORD: Specifies the SMTP credential password from Step 2.
    • Keep default values for SMTP_HOST_PORT parameter (587) and SMTP_TLS_MODE parameter (STARTTLS).
    For example:
    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;
    /
  5. Send a test email using APEX SQL Workshop, SQL Commands specifying one of the approved senders from Step 3 as "From". For example:
    BEGIN
        APEX_MAIL.SEND(p_from => 'alice@example.com',
                       p_to   => 'bob@example.com',
                       p_subj => 'Email from Oracle Autonomous Database',
                       p_body => 'Sent using APEX_MAIL');
    END;
    /
  6. To monitor email delivery in your Application Express instance:
    1. Sign in to APEX Administration Services.
    2. Open the Manage Instance page.
    3. Click the Mail Queue link in the Manage Meta Data section.
    Alternatively, query APEX_MAIL_QUEUE and APEX_MAIL_LOG views using a SQL client.

Note:

There is a limit of 5,000 emails per workspace in a 24-hour period. Oracle Cloud Infrastructure Email Delivery may impose additional limitations.

For more information, see: