Integrating Oracle Application Express with Email Delivery

Use Oracle Application Express to send emails through the Email Delivery service.

Configure Oracle Application Express to Send Email Through Email Delivery

You can use the APEX_MAIL package to send emails from Oracle Application Express applications deployed in Autonomous Transaction Processing. See Autonomous Database for Transaction Processing and Mixed Workloads and Provision Autonomous Database for more information.

Important

These instructions contain sample code for your convenience and should be used as a reference. For client support, you must contact Oracle Application Express customer support. These steps were tested on an Oracle Linux Server release 7.9 compute instance.

Before you use APEX_MAIL you must configure Oracle Cloud Infrastructure Email Delivery in your Application Express instance.

To enable APEX_MAIL functionality in your Application Express instance in Autonomous Transaction Processing:

  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. See Configuring 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 Create 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. Note

    We recommend you create credential objects to store usernames and passwords and securely pass the credential objects to set SMTP Authentication. For more information, see Use Credential Objects to set SMTP Authentication.
    Or, connect to your Autonomous Transaction Processing as ADMIN user using 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 username from Step 2.
    • SMTP_PASSWORD Specifies the SMTP credential password from Step 2.

    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.

      Or, query APEX_MAIL_QUEUE and APEX_MAIL_LOG views using a SQL client.