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.

13 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

Carsten Cerny said...

Hi Nico, hi Andre,

my Home Region is Frankfurt too. In the beginning of my project I added Australia East (Sydney) to my regions.

In the top menu I can see my Home Region "Frankfurt" and the next entry is "Australia East (Sydney)". When I click on Australia nothing changed/happend.

I also pushed the right command "APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.ap-sydney-1.oraclecloud.com');"

But when I click on Email Delivery it is showing that no email function is available in Frankfurt. How could I tell the system that my second region should be used?

I also tried to login via "https://login.ap-sydney-1.oraclecloud.com" but after entering my cloud tenant name it changed to Frankfurt URL.

Best,
Carsten

cruz said...

Oracle said the free cloud can send 1000 emails per month but the Email Delivery
service is showing that the account have to upgrade to a paid account before you can send mail.

Anonymous said...

This article helped me to send mail from Oracle Cloud - Oracle Apex Application.
That's Great. Only thing to remember in smtp host ; region host name should be give carefully as mentioned in the article.

one thing I want to know mails are going in Spam.
second thing can i give my own (domain)credentials) to send emails.

Alex said...

Thank you.
Did you get any issue verifying your email? Mine's stuck on 'Verification Pending'.

Unknown said...

I am in a simmular situation as Niko.
My Apex app runs in my Home Region "Frankfurt", in wich email delivery Service is not available.
So I also subscribed to uk-london-1. In the London Region I created an Email approved Sender and i set the SMTP_HOST_ADDRESS to smtp.email.uk-london-1.oci.oraclecloud.com.
But if I now try to send a Mail i dont get any immediate responce. But after a few seconds to minutes I get "ORA-29278: SMTP transient error: 421 Service not available".
My approved Senders Mail domain is registerd under World4you(where I added the spf record) and uses Microsoft office 365 as Mail service.

I hope someone can tell me what I did wrong.

Best
Michael

Nahid said...

Hi
Thanks for the post .
Does this solution work with free trail ATP account?

Thanks
Nahid

Dimitri Gielis said...

Yes, you get 1000 emails for free.

Joost André said...

Hi Dimitri,
Thanks a lot for your great blog's about the Free Tier!
I almost finished them all.
At the moment I got stuck in the email-part.

"Email Delivery is not available with your current permissions.
Please Contact Support for further assistance."

Any idea on this one?

Kind regards, Joost

Joost André said...

And in the meantime:

From Oracle Support
CAUSE
Cause for this error is because an IDCS user is being used to access this service. A native user needed to access Email Delivery service.
SOLUTION
Follow step creates native user:

From OCI Menu Choose Identity > Users > Create User
Select the Created User and press Create/Reset Password
Copy the password
Copy the URL of the page (https://console.XX-XXXX.oraclecloud.com)
Start a new browser tab with copied URL and sign with the created user and password.
Select Email Deliver service.
Once the user is created, to setup Email Delivery, complete the following steps:

See: https://docs.cloud.oracle.com/iaas/Content/Email/Concepts/overview.htm

Generate SMTP credentials for a user.
Create an approved sender.
Configure SPF.
Configure the SMTP connection.

Anonymous said...

I also have Apex running in Germany Central (Frankfurt) and had problems trying to get emails sent through using Email Delivery in UK South (London) through smtp.email.uk-london-1.oci.oraclecloud.com. It would always fail with "ORA-29278: SMTP transient error: 421 Service not available".

However, after setting up Email Delivery in US East (Ashburn) and configuring Apex to send emails through smtp.us-ashburn-1.oraclecloud.com everything seems to work just fine.

So the problem seemed to be particular to trying to use Email Delivery in London from Apex in Frankfurt.