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:
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
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:
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:
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.
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:
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:
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;
/
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:
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;
/
And so now, you are all ready to send emails from your Oracle APEX applications deployed in the Autonomous Database!
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
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:
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:
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.
Finally, you place a new order and verify if the confirmation email has been sent. Perform the following steps:
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.