Free Oracle Cloud: 11. Sending Emails with APEX_MAIL on ATP

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In this post, we will configure the Oracle Cloud to support our instances, databases and Oracle APEX to send out emails. In my blog post 5. Setup APEX in ATP and create first APEX app, I initially said you can't use APEX_MAIL in APEX in ATP, but I was wrong, so I few days after my post I updated it, to point you to the documentation with the configuration steps you have to do to make it work.

The reason I thought you can't use APEX_MAIL was that during my tests, sending emails failed. I hadn't read the documentation ;) In this post, I will share how I got it to work after all.

The first thing you have to do is create an SMTP Credential for your user. You do that by logging into your Oracle Cloud account, go to Identity > Users and select your user:

Click the SMTP Credentials in the left menu:

Hit the Generate SMTP Credentials button, and give it a name:

On the next screen, you will see the USERNAME and PASSWORD. Take a note of this as you won't get to see it anymore after:

You will come back to the overview screen, but again, as far as I can see there's no way to get the password again, so if you lose it, you need to set up one again:

Now we will let Oracle APEX know about those parameters. Login as ADMIN through SQL Developer for example (see this blog post on how to do that) and run the following statement:

BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.us-ashburn-1.oraclecloud.com');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1..xxxxxxxxxxx@ocid1.tenancy.oc1..xxxxxxx');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'Fxxxx');
  COMMIT;
END;
/


Here is a screenshot when running:

Log in to Oracle APEX, go to SQL Workshop and try to send out emails:

It says statement processed, but when you query the APEX_MAIL_QUEUE, you will see it's still stuck with an error ORA-29278: SMTP transient error: 471 Authorization failed:

There's one more step you have to do, specify the email addresses you want to allow to send emails from. Go in your Oracle Cloud Account Dashboard to Email Delivery and click the Email Approved Senders and hit the Create Approved Sender button

Add the email address you want to send emails from and hit the Create Approved Sender button:

In the overview you will see all allowed email addresses:

When we try to send again and check the APEX_MAIL_LOG, we see the emails are effectively sent:

That's it, you can now send emails out of your APEX apps :)

We are almost done with the series. In the next post we will create a second compute instance and set up a Load Balancer.