Friday, October 04, 2019

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.

3 comments:

Niko said...

Thank you for the blogpost!

It seems that unfortunately on eu-frankfurt E-Mails are not (yet) available...

Regards
Niko

Niko said...

Thank you for the blog post!

It seems that on region eu-frankfurt-1 E-Mails are not (yet) available...

Regards
Niko

Anonymous said...

Hi Nico,
You have to also subscribe one of the regions that are currently valid;
(sadly not all are valid/possible today)
And be sure to also use this region for this:
APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.us-phoenix-1.oraclecloud.com');
For me this one works and I can send mail now:
One more thig to consider.
If you don’t take care another step, then the mails remain in the INTERNAL mail Queue as long
If you don’t take care another step, then the mails remain in the INTERNAL mail Queue.
1. you create a scheduled job to "really" send out by running APEX_MAIL.PUSH_QUEUE.
2. you call APEX_MAIL.PUSH_QUEUE; after your call(s) of apex_mail.send;
Regards
Andre