X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Build, test and deploy apps on Oracle Cloud. Start Now

Sending Email from your Oracle APEX App on Autonomous Database

Chaitanya Koratamaddi
Principal Product Manager, Oracle APEX | Database Tools

Do you know how easy it is to send emails from your Oracle APEX apps deployed in Oracle Autonomous Database? This blog post provides you the simple steps involved in this process. 

This blog post assumes that you already have an Oracle Autonomous Database instance (ATP / ADW) up and running and an Oracle APEX workspace created within it. If you do not have an Oracle Autonomous Database yet, and want to try it for free, you can sign up for the Oracle Cloud Free Tier account. To learn about how to create an Autonomous Database instance and an APEX workspace within it, see this tutorial.

Note:

  • The instructions in this blog post use the free promotion account that is within the trial period. When your trial is over, your account will be limited to Always Free resources. You need to upgrade to a paid account to continue to use the Email Delivery. 
  • If Email Delivery is not available in your current region, you may need to subscribe to additional OCI regions.
  • The application that is sending email does not have to be in the region where email is sent for delivery.
  • There is a limit of 5000 emails per workspace in a 24-hour period. 

You can use the APEX_MAIL package to send emails from your Oracle APEX applications deployed in Oracle Autonomous Database (ATP / ADW). However, to use the APEX_MAIL package, you need to first configure an email service provider in your Oracle APEX instance. Currently, the only supported email service provider is the Oracle Cloud Infrastructure Email Delivery service. 

Sending email from your APEX app in Autonomous Database involves the following four steps: 

1. Generating Simple Mail Transfer Protocol (SMTP ) Credentials for Email Delivery

2. Creating an approved sender for Email Delivery

3. Configuring the SMTP parameters in the Application Express instance

4. Sending a test email using Oracle APEX SQL Workshop

1. Generating SMTP Credentials for Email Delivery:

SMTP credentials are necessary to send email through Email Delivery service. When you send an email from your APEX application,  the APEX instance uses these SMTP credentials to authenticate with email delivery servers. In this section, you generate SMTP credentials for your user in the OCI console. Perform the following steps:

  1. Sign in to Oracle Cloud.
  2. In the left menu, navigate to Identity > Users and select the username for which you need to generate SMTP credentials.
  3. In the left menu, under Resources, click SMTP Credentials and then click Generate SMTP Credentials.
  4. Enter a meaningful description and click Generate SMTP Credentials again.
  5. Copy the Username and Password and make sure you save them to a text file. You will be using these credentials again in a later step. Now, close the dialog.

2. Creating an Approved Sender for Email Delivery:

Next, you set up an approved sender for Email Delivery.  You must set up an approved sender for all email addresses you use as the "From" with APEX_MAIL.SEND calls. Perform the following steps:

  1. In the left menu, navigate to Email Delivery > Email Approved Senders.

     
  2. Click Create Approved Sender.
  3. In the Create Approved Sender dialog, enter your email address (valid email address of the sender) and click Create Approved Sender.

You need to identify the SMTP connection endpoint for Email Delivery. You will configure this endpoint as SMTP Host in the APEX instance in the next section. Under Email Delivery, click Email Configuration. Make sure you copy the SMTP Server Name (for example, smtp.us-phoenix-1.oraclecloud.com) and save it to a text file.

3. Configuring the SMTP Parameters in the Application Express Instance:

Now that you have the SMTP credentials generated, you need to let your Application Express instance know about these parameters. To do this, you connect to your Autonomous 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
  • SMTP_USERNAME: Specifies the SMTP credential user name 
  • SMTP_PASSWORD: Specifies the SMTP credential
  • Default values for SMTP_HOST_PORT parameter (587) and SMTP_TLS_MODE parameter (STARTTLS).

To connect to the Autonomous Database instance, you can use the built-in SQL Developer Web. You can access SQL Developer Web either from the OCI console or from the ATP / ADW Service console. 

Perform the following steps:

  1. From the left menu, navigate to your Autonomous Database instance details page
  2. Click the database instance name.
  3. The instance details page displays. Click Tools and then click Open SQL Developer Web.
  4. Enter ADMIN for Username, <your password> for Password and click Sign In.
  5. In the Worksheet area, enter the following code and click Run Statement. 

BEGIN
 APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'SMTP  SERVER NAME copied and saved in the above section');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'Username copied and saved in the above section');
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'Password copied and saved in the above section');
COMMIT;
END;
/  



 

4. Sending a Test Email using Oracle APEX SQL Workshop

Finally, you want to test if the email delivery works. In this step, you navigate to Oracle APEX > SQL Workshop > SQL Commands and send a test email. You specify the approved sender that you created in the "Creating an Approved Sender for Email Delivery" section above. Perform the following steps:

  1. From the left menu, navigate to your Autonomous Database instance details page
  2. Click the database instance name.
  3. The instance details page displays. Click Tools and then click Open APEX.

  4. Log in to your workspace.
  5. Click SQL Workshop and then click SQL Commands.
  6. Enter the following code in the SQL Commands area and click Run.

    BEGIN 
    apex_mail.send(p_from => 'approved sender email address', 
    p_to => 'recipient email address', 
    p_subj => 'Email from Autonomous',
    p_body => 'This is a test email from Autonomous');

    apex_mail.push_queue(); 
    END; 
    / 


  7. Now, verify if the email has been successfully delivered to the recipient's email address specified in the code snippet above.

  8. You want to monitor the email delivery in your Application Express instance as well. You can simply query the APEX_MAIL_LOG and APEX_MAIL_QUEUE views.
    Note: Alternatively, you can monitor the email delivery in the APEX instance administration. To do this, sign in to Administration Services and click Manage Instance. Under Manage Meta Data, click Mail Queue.

And so now, you are all ready to send emails from your Oracle APEX applications deployed in the Autonomous Database! 

Sending Email from the Sample Database Application: Example

This example is just a simple extension to the above steps and you use the Sample Database Application. You enable the Sample Database Application to send an email whenever a new order has been placed by a customer. First, you add yourself as a new customer. Then, you navigate to the Order Summary page (Page 14), and create a PL/SQL process. This PL/SQL process sends an email notification upon placing a new order. Once this is created, you then navigate to the ORDERS page and place an order. Upon clicking the Complete Order button in the wizard, you receive an email notification with the order details.

If you already have the Sample Database Application installed, you can skip the first section.

1) Installing the Sample Database Application 

In this section, you install the Sample Database Application. Perform the following steps:

  1. Log in to your APEX workspace and navigate to App Builder.
  2. Click App Gallery and then select Sample Apps.
  3. Click Sample Database Application and then click Install App.
  4. Click Next and then click Install App.
  5. The application is installed now. Click Run.
  6. Log in with your workspace username and password.

2) Adding a New Customer

Next, add a new customer in the Sample Database Application. The email address that you provide here is the one that would receive the order confirmation email. Perform the following steps:

  1. In the left navigation menu, click Customers.
  2. Click Create Customer.
  3. In the Customer Details dialog, add yourself as a customer. Enter the values for the mandatory fields, make sure you enter a valid email address and then click Add Customer.

3) Creating a PL/SQL Process to Send Email Confirmation

Now, add a PL/SQL process to the Order Summary page that sends a confirmation email to the customer. Perform the following steps:

  1. In the left navigation menu, click Orders.
  2. In the Developer Toolbar, click Application <n>.
  3. In the Application Home page, click Page 14.
  4. In the Page Designer, click the Processing tab. Expand Processing, right-click Processes and select Create Process.

  5. In the Property Editor, enter Send Email Confirmation for Identification > Name.
  6. Under Source, click the PL/SQL Code Editor icon. Enter the following PL/SQL code and make sure you replace the approved sender email address with the one that you created in the OCI console earlier. Then, click OK.

     

    FOR c1 IN 
    ( 
           SELECT cust_email 
           FROM   demo_customers 
           WHERE  customer_id = :P14_CUSTOMER_ID) 
    LOOP 
      IF c1.cust_email IS NOT NULL THEN 
        apex_mail.send(p_from => 'approved sender email address', 
                       p_to => c1.cust_email, 
                       p_body => 'Your order has been received and ' 
                       ||chr(10) 
                       || 'will be processed shortly.', 
                       p_subj => 'Order Number ' 
                       ||:P14_ORDER_ID 
                       ||' received.');
    END IF;
    END LOOP;

     

    In the Property Editor, scroll down to Success Message. Enter <br> Confirmation Email Sent! for Success Message.
  7. For Error message, enter Confirmation Email not Sent!
  8. Scroll down to Server-side Condition. Select PLACE_ORDER for When Button Pressed.

  9. Click Save.

4) Verifying the Email Delivery

Finally, you place a new order and verify if the confirmation email has been sent. Perform the following steps:

  1. Navigate to the application running environment. In the left menu, click Orders. 
  2. In the Orders page, click Place Order.
  3. In the Identify - Customer dialog, accept the default selection for Create Order for: and then select your name (or the customer that you added in step 9) from the Customer select list. Click Next.
  4. In the Order - Items dialog, select quantity of items from Add to Cart select list and click Next.
  5. Click Complete Order. In the confirmation dialog, click OK.
  6. Now you see a confirmation that the email has been sent. Close the dialog.

  7. Navigate to SQL Workshop > SQL Commands and query the APEX_MAIL_LOG view.

  8. You may want to verify your email inbox (for the valid customer email address that you added in the section above) and see that the email is delivered.

In this blog post, you learned the steps involved in sending an email from your Oracle APEX application deployed in  Oracle Autonomous Database. First, you generate SMTP credentials and create approved senders for Email Delivery. Then, you set these SMTP parameters to the Application Express instance and finally test by sending an email using Oracle APEX SQL Workshop. The blog post also covered a simple example of sending email from the Sample Database Application.

Additional Information:

Oracle Application Express is a high productivity platform for creating declarative, low code Web applications on the Oracle Database and in the Oracle Cloud. To learn more about Oracle Application Express, visit apex.oracle.com. To sign-up for an always-free autonomous database in the Oracle Cloud, visit oracle.com/cloud/free. To learn more about Oracle APEX on Autonomous Database, visit apex.oracle.com/autonomous. For more information about APEX_MAIL, see Oracle Application Express API Reference. For more information about sending email from Oracle Application Express on Autonomous Database, see the Oracle Cloud documentation.

Recent Content