Send Email from Oracle APEX

You can use the APEX_MAIL package to send email from Oracle APEX applications deployed in Autonomous AI Database.

Before you use APEX_MAIL you must configure an SMTP server that will deliver email from Oracle APEX. Oracle APEX on Autonomous AI Database supports Oracle Cloud Infrastructure Email Delivery service as well as third party email providers.

Configure OCI Email Delivery

Your Oracle APEX instance can utilize OCI Email Delivery service to send email from APEX applications.

To configure APEX_MAIL functionality to use OCI Email Delivery service:

  1. Identify the SMTP connection endpoint for Email Delivery. You configure the endpoint as the SMTP Host in your APEX 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 APEX instance uses credentials to authenticate with Email Delivery servers when you send email. See Creating SMTP Credentials 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 AI Database 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

    • SMTP_HOST_PORT: Keep the default value for this parameter (587)

    • SMTP_TLS_MODE: Keep the default value for this parameter (STARTTLS)

    For example:

    BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.us-phoenix-1.oci.oraclecloud.com');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1.username');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'password');
  COMMIT;
END;
/

    See APEX_INSTANCE_ADMIN for more information.

See Overview of Email Delivery for more information.

Configure Third Party Email Provider

Your Oracle APEX instance can utilize third party email providers to send email from APEX applications.

Before configuring a third party email provider in APEX, ensure the following prerequisites are met:

See Configure Network Access with Private Endpoints for more information.

After the prerequisites are met:

  1. Add the following access control list for the SMTP endpoint as ADMIN (note the private_target parameter):

    BEGIN
  DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => 'smtp.example.com',
    ace => XS$ACE_TYPE(
      privilege_list => XS$NAME_LIST('SMTP'),
      principal_name => APEX_APPLICATION.g_flow_schema_owner,
      principal_type => XS_ACL.ptype_db),
    private_target => true);
END;
/

    Note: If you set ROUTE_OUTBOUND_CONNECTIONS database property, you do not need to define access control lists for individual SMTP endpoints in order to access them from APEX. See Enhanced Security for Outbound Connections with Private Endpoints for more information.

  2. Connect to your Autonomous AI Database as ADMIN user using a SQL client and configure the following SMTP parameters using APEX_INSTANCE_ADMIN.SET_PARAMETER. If your third party email provider does not require authentication, skip the SMTP_USERNAME and SMTP_PASSWORD parameters.

    • SMTP_HOST_ADDRESS: Specifies the SMTP connection endpoint from Step 1

    • The valid values for SMTP_HOST_PORT parameter are 25 or 587

    • Set the value for the SMTP_TLS_MODE parameter to Y

    For example:

    BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.example.com');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_PORT', '587');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_TLS_MODE', 'Y');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'username');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'password');
  COMMIT;
END;
/

    See APEX_INSTANCE_ADMIN for more information.

Validate Email Configuration

Shows the steps to confirm your email provider is ready to use with APEX_MAIL.

  1. Validate the email configuration settings using a SQL client as ADMIN:

    BEGIN
  APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG;
END;
/

    If any errors are reported (for example, "ORA-29279: SMTP permanent error: 535 Authentication credentials invalid"), adjust the SMTP parameters or Virtual Cloud Network (VCN) configuration and repeat the validation step.

    See APEX_INSTANCE_ADMIN for more information.

  2. Send a test email using APEX SQL Workshop, If you are using OCI Email Delivery, ensure the p_from parameter matches one of the approved senders. For example:

    BEGIN
  APEX_MAIL.SEND(
    p_from => 'alice@example.com',
    p_to   => 'bob@example.com',
    p_subj => 'Email from Oracle Autonomous AI Database',
    p_body => 'Sent using APEX_MAIL');
END;
/

    See APEX_MAIL for more information.

  3. To monitor email delivery in your APEX 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.

Notes for Sending Email from Oracle APEX

Provides notes for working with email providers in Oracle APEX from an Autonomous AI Database instance.

Note the following when working with email providers in Oracle APEX: