If you’ve been following this series, you now have:

• A container running Oracle 23ai & APEX 24.1

• A container running ORDS

• A self-signed certificate allowing secure access via ORDS

While the goal is to have a truly portable development environment, there’s some things that do need a network. One of those things that most applications will need is the ability to send emails.

APEX has always had the ability to send emails - specifically using the APEX_MAIL API. Sure, you need to point to a configured mail server, but once you have one of those, it’s trivial to configure APEX to send emails.

While we could create another container and configure something like sendmail there, that would only work if it could talk to other mail servers, and also needs the internet to function. That sounds like a lot of work.

Instead, since we need the internet to send email, let’s just point to an existing internet-based email service instead. In this case, we’ll use the Oracle Email Delivery service as part of the “Always Free” tier on OCI.

If you haven’t created your own free tier by now, you ought to check it out. While it does ask for a credit card while signing up, as long as you stick to the “Always Free” resources, you should not be charged anything. I’ve been using it since 2019 and have paid $0.

Oracle’s Email Delivery service is included in the Always Free tier, meaning that you can use it anytime you like for as long as you like for free. There is a limitation of 100 emails per day, however. For most developers, this is likely an acceptable limit. If you need to send more than 100 per day, you can upgrade your free tier to a regular tenancy, where you will get your first 3,000 emails for free and then pay 8.5 cents per 1,000 additional messages.

Email Configuration Overview

There’s three parts to configuring the Email Delivery service. The configuration is split across OCI, the Oracle Database and Oracle APEX. Let’s walk through each of them.

Steps for OCI

To configure the Email Delivery service, you’ll need administrator-level access to your OCI tenancy. If you’re using the Always Free tier, the account that you typically use to manage things should suffice.

Login to the OCI Console

To login to the OCI Console:

1. Navigate to https://cloud.oracle.com

2. Enter your Cloud Account Name and click Next. This is also the name of your tenancy that you created when you signed up for the Free Tier.

3. Select an Identity Domain and click Next. Typically, this is set to Default.

4. Enter your Username and Password and click Sign In.

5. Allow the login event to occur using Oracle Mobile Authenticator.

You should now see the main page of the OCI Console:

Note: Some tenancies have an upgraded version of the OCI Console home page. Navigation will be slightly different if that is the case.

Create a Group

To start, let’s create a Group. This should be in the same domain that you want to create the user in. Groups are similar to database roles. They are nothing more than a container used to associate users and privileges. Like database roles, Groups can be named anything at all, but won’t inherit any specific privileges until explicitly granted.

To create a Group:

1. From the main menu, select Identity & Security. When the next window appears, under the Identity section, select Domains.

2. On the next screen, choose the Compartment that you want to create all of these resources in. It doesn’t really matter which one you use, as long as you keep them all in the same one.

3. Select the Domain that you want to create the Group in. To keep things simple, we’ll simply use the Default domain, which is also the current one.

4. Click on the Groups tab to see all current Groups.

5. Click Create group.

6. Enter approvedSenders for the Name and Description and click Create.

We now have a new Group. We’ll map this group to the user we create in just a bit.

Create a Policy

Next, we need to create a Policy.

Policies are similar to database privileges. A user that has a policy granted to them will inherit the specific resources referred to in the policy.

Unlike database privileges, policies use their own vernacular when creating them. This post won’t get into the specifics, but you can read more about policy syntax here.

Let’s create a policy to allow our group to send email.

1. Use the Breadcrumbs at the top of the page and click on Domains.

2. Click the Policies tab and then click Create Policy.

3. Enter approvedSenders for the Name and Description, select your Compartment, and then click on Show manual editor.

4. When the text area appears, enter the following text:

Allow group approvedSenders to use approved-senders in compartment <Compartment Name>

Be sure to replace <Compartment Name> with the name of your compartment.

6. Click Create.

Create a User

Next, we need to create a user that will be allowed to send emails. The email address of this user will need to be set in APEX as the designated sender, and only this email will be allowed to perform that role.

1. Click on the Domains tab and select the domain that you created the Group in earlier.

2. Click on the Users tab and click Create user.

3. Enter Email for the First name, Manager for the Last name, and the email address that you want to send from for the Username / Email.
   Note: you can use any email address here. This will be the address in the From field of all emails sent, so choose wisely.

4. Make sure the Use the email address as the username is checked.

5. In the Groups section, be sure to check approvedSenders.

6. Click Create.

Next, we want to remove unnecessary capabilities from this user, since all they will really need to do is store the SMTP credentials.

7. Edit the new user.

8. Click Edit user capabilities.

9. Uncheck all options except SMTP credentials.

10. Click Save changes.

Create SMTP Credentials

Next, we need to create a set of SMTP credentials and associate them with this user. These credentials will also need to be added to APEX and serve as the username and password to access the Email Delivery service with.

1. In the Resources section, select SMTP credentials.

2. Click Generate credentials.

3. Enter SMTP credentials for the Description and click Generate credentials.

On the next screen, it will display the SMTP Username and Password.

4. Copy both the Username and Password to somewhere that you can refer to them later.

Note: This will be the ONLY time you can copy the password, so be sure to copy it before closing this window.

Add Approved Sender

Next, let’s add the email address of the user we just created to the list of Approved Senders. This is the final link that will allow us to send emails from APEX & PL/SQL applications.

1. From the main menu, select Developer Services. When the next window appears, under the Application Integration section, select Email Delivery.

2. Be sure that the correct Compartment is selected.

3. Click the Approved Senders tab.

4. Click Create Approved Sender.

5. Enter the email address of the user that was just created and click Create Approved Sender.

Copy Email Delivery Endpoint

One last bit of information we’re going to need is the SMTP Public Endpoint. This is essentially the address of the Email Delivery service that we will need to add to APEX in just a bit.

To get the SMTP Public Endpoint:

1. Click on the Configuration tab.

2. Under the SMTP Sending Information region, record the value of the Public Endpoint and SMTP Ports. We will need to add this to APEX in the next section.

That concludes the steps that need to be performed in the OCI Console.

Steps for APEX

Now that we have the underlying components configured, all we need to do in APEX is enter them in the corresponding fields in the Instance Administration application.

1. Login to the INTERNAL workspace as the ADMIN user in APEX.

2. Click on Manage Instance.

3. Click on Instance Settings.

4. In the Email section, enter the following values:

Note that most values will need to be replaced with the ones derived in earlier steps.

5. Click Apply Changes.

Since we’re configuring this at the instance level, these settings will apply to all workspaces in the instance of APEX. They can be overridden by passing in new values to the APEX_MAIL API.

Steps for the Oracle Database

There’s one more step at the database level that needs to be performed. We need to create an ACE (access control entry) so that APEX can call the Email Delivery endpoint.

1. Connect to the database via SQLcl as the SYS user.

2. Ensure that you’re connected to the correct PDB. In this case, it should be freepdb1.

3. Run the following SQL statement:

begin
  dbms_network_acl_admin.append_host_ace (
    host       => 'smtp.email.us-ashburn-1.oci.oraclecloud.com',
    lower_port => 587,
    upper_port => 587,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'APEX_240100',
                              principal_type => xs_acl.ptype_db));
end;
/

Note: you may need to change the value of the host parameter, based on what your SMTP Public Endpoint is.

That’s it! All three parts - OCI, APEX & Oracle Database - are all configured to send email from your local instance via the Email Delivery service!

Testing It All Out

Now that everything is configured, let’s test it out. To do this, all you will need is an APEX workspace on your local development environment. Any workspace aside from INTERNAL will do.

1. Login to any workspace (aside from INTERNAL) on your local APEX environment.

2. Click on SQL Workshop.

3. Click on SQL Commands.

4. Enter the following SQL:

begin

-- send a message
apex_mail.send
  (
   p_from => '<email address of user created>'  -- replace this value
  ,p_to   => '<your email address>'             -- replace this value
  ,p_subj => 'Test Email Message'
  ,p_body => 'This is a test email message sent via OCI Email Delivery'
  );

-- push the email queue now
apex_mail.push_queue; 

end; 
/

5. Be sure to replace the p_from & p_to parameters with the email address that was created for the user and any email address that you have access to, respectively.

6. Run the SQL.

If everything works, you will receive an email with the above subject & body in just a few seconds.

In the case that you don’t receive an email, check the view APEX_MAIL_QUEUE - specifically the column MAIL_SEND_ERROR - for more information.

Summary

Our little development environment leveled up once again, and can now send email messages thanks to the OCI Email Delivery service. With just a few steps, we were able to create a user, group, policy, SMTP credential and then map all of that to our APEX instance. This is just another example of just how well you can extend the capabilities of APEX with a variety of OCI services.

Best of all - all of this can be done at no cost using the OCI Free Tier!

Title Photo by Ethan Hoover on Unsplash