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.

34 comments:

  1. Thank you for the blogpost!

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

    Regards
    Niko

    ReplyDelete
  2. Thank you for the blog post!

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

    Regards
    Niko

    ReplyDelete
  3. 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

    ReplyDelete
  4. 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

    ReplyDelete
  5. 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.

    ReplyDelete
  6. 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.

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

    ReplyDelete
  8. 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

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

    Thanks
    Nahid

    ReplyDelete
  10. Yes, you get 1000 emails for free.

    ReplyDelete
  11. 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

    ReplyDelete
  12. 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.

    ReplyDelete
  13. 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.

    ReplyDelete
  14. Hi Dimitri,
    I have just set up everything with all the settings as noted above. Unfortunately, still getting a ORA-29278: SMTP transient error: 421 Service not available.

    Also to add : I have integrated "Postfix with Email Delivery Configure Postfix to Send Email Through Email Delivery" from this link https://docs.cloud.oracle.com/iaas/Content/Email/Reference/postfix.htm

    Email sending works from the Linux command line, just not from APEX.

    Ran the following, noting that the SMTP host address is noted in the Oracle Email Delivery settings as smtp.email.uk-london-1.oci.oraclecloud.com

    BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS' , 'smtp.email.uk-london-1.oci.oraclecloud.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME' , 'ocid1.user.oc1..aaaaaaaaxxxxxxxxxxxxxxxxx@ocid1.tenancy.oc1..aaaaaaaaxxxxxxxxxxx.xh.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD' , 'xxxxxxxxx');
    COMMIT;
    END;
    /

    Any ideas on what might be going wrong ? Thanks for your time putting together a great post!

    ReplyDelete
  15. Hi

    I too am having issues with getting emails to send. (I actually upgraded to the paid service, hoping I won't get billed if less than 1000 / month).

    I configured as in the post, set up STMP Credentials, email apprpved sender and configured instance as below:


    BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.uk-london-1.oci.oraclecloud.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1..aaaaxxxxxxxxxxxxxxxxocid1.tenancy.oc1..axxxxxxxxxxxxxdq.gz.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'xxxxxxxxxxxxxx');
    COMMIT;
    END;
    /


    However am still getting error "ORA-29278: SMTP transient error: 421 Service not available" when querying the apex_mail_queue.

    Any thoughts on where I'm going wrong here very much appreciated!

    Thanks
    Dave

    ReplyDelete
  16. Hi

    I too am having issues with getting emails to send. (I actually upgraded to the paid service, hoping I won't get billed if less than 1000 / month).

    I configured as in the post, set up STMP Credentials, email approved sender and configured instance as below:


    BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.uk-london-1.oci.oraclecloud.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1..aaaaxxxxxxxxxxxxxxxxocid1.tenancy.oc1..axxxxxxxxxxxxxdq.gz.com');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'xxxxxxxxxxxxxx');
    COMMIT;
    END;
    /


    However am still getting error "ORA-29278: SMTP transient error: 421 Service not available" when querying the apex_mail_queue.

    Any thoughts on where I'm going wrong here very much appreciated!

    Thanks
    Dave

    ReplyDelete
  17. Follow steps as suggested but not able to send mail.
    see all mails struck in "select mail_SUBJ, mail_from, mail_message_created, mail_send_error from apex_mail_queue" with no error in mail_send_error.

    No data found in Select * from APEX_MAIL_LOG.

    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.email.eu-frankfurt-1.oci.oraclecloud.com');

    While user is in Mumbai region.

    Please help.

    ReplyDelete
  18. Dear Dimitri,
    I have followed all steps and I am using the autonomous TP database on the cloud.
    I have executed the code to add ACL in both APEX_190100 and the workspace schema:

    BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
    host => '*',
    ace => xs$ace_type(privilege_list => xs$name_list('connect’, 'resolve','smtp'),
    principal_name => 'APEX_190100',
    principal_type => xs_acl.ptype_db));
    END;

    and in the apex_mal_queue I am getting the error:
    ORA-24247: network access denied by access control list (ACL)

    Any ideas?
    my email delivery is from:
    smtp.email.eu-frankfurt-1.oci.oraclecloud.com

    ReplyDelete
  19. at always free tier it happening

    The limit for this compartment has been exceeded.
    when i create approved sender

    ReplyDelete
  20. What is the process to add smtp.gmail.com to always free tier ?

    ReplyDelete
  21. Dear Dimitri,

    under Manage Instance there is no
    1.Instance Settings (which is need for private smtp setting )
    2.Security (to control file upload etc)
    3.Workspace Purge Settings (language task)

    its seems oracle gives nothing about apex at always free tier.
    its not helping developers to bring some client to oracle cloud ,
    its not helping developers to start a startup.

    please take a step , otherwise its good for nothing ...

    ReplyDelete
  22. Hi Dimitri!

    Please note that Oracle Email Delivery Service is not available in Free Tier. This is the response I got in a Service Request:
    Reviewing your account with our internal teams, we were able to confirm that the error you received is due to your account now being listed as 'free tier'. Email Delivery is not included in the 'free tier' of service. With that said, we can certainly increase your Email Delivery limits in order to allow you to continue sending emails, however this would cause you to be billed for the services.

    The SMTP error message was: ORA-29278: SMTP transient error: 455 Maximum messages sent per minute reached : limit is 0

    Regards,

    ReplyDelete
  23. Alex Maycon da Silva3/06/2020 3:08 PM

    Hello Dimitri!

    I have a problem... https://community.oracle.com/message/15572475#15572475

    Can you help?

    Thanks!!!

    ReplyDelete
  24. Thanks for yet another wonderful post Dimitri.
    For all those getting a "ORA-29278: SMTP transient error: 421 Service not available" error.
    Please check your SMTP_HOST_ADDRESS very carefully referencing the documentation found below as the format changes by region.
    https://docs.cloud.oracle.com/iaas/Content/Email/Concepts/overview.htm
    Regions and Availability Domains list where email is available.
    SMTP Authentication and Connection Endpoints provides the required values.
    For example, to get email working on my London region environment I had to change
    'smtp.us-ashburn-1.oraclecloud.com'
    not to 'smtp.uk-london-1.oraclecloud.com' as you would expect
    but to 'smtp.email.uk-london-1.oci.oraclecloud.com'.
    Hope this helps.

    ReplyDelete
  25. You say this works on always free, but reading always free cannot allow email delivery.

    there is another way to send email, without using oracle email delivery?

    Email delivery cannot be used because, email sender approve cannot be created.

    thanks,

    ReplyDelete
  26. In the first month you can setup the approved list.
    After that if you provide a credit card, it will be enabled and you can set it up too.
    You won't be charged for the first 1000 emails (or read the documentation about the exact number that you get at the time you read this).

    Hope that helps,
    Dimitri

    ReplyDelete
  27. In my experience always free account does not allow you to send emails after trial expires. As already mentioned above "ORA-29278: SMTP transient error: 455 Maximum messages sent per minute reached : limit is 0"

    regards
    Martin

    ReplyDelete
  28. hey! not working ):
    when i try to run the first code its ask for "substitution variables"

    ReplyDelete
  29. Hi Unknown,

    Probably there is an '&' in your SMTP username or SMTP password
    Put this before the 'BEGIN':

    SET DEFINE OFF;

    Regards,
    Jeroen

    ReplyDelete
  30. Hi dimitri

    thanks for your blog is very helpful, i have one question, this steps and configure of smtp server works for apex implemented over OCI dbsystem ?? is the same cofiguration for the user ??

    thank in advance for any help

    Fredy V

    ReplyDelete
  31. Hi Dimitri,

    This worked perfectly!

    Thanks,
    Marcelo

    ReplyDelete
  32. https://jeffkemponoracle.com/2016/04/email-made-easier/

    ReplyDelete
  33. Hi Dimitri and the team,
    I found this post very useful, but I guess a few things changed since 2019.
    So, as of today, in addition to everything above ( Jan 2023):

    1. You should create a Group ("Email_group" for example), include your user in the group and create a Policy to allow this group to send Emails, for example:

    Allow group Email_group to manage email-family in tenancy

    2. Your STMP host and port are visible in Email Delivery/Configuration

    3. I found it super useful to test with CURL before configuring APEX. Gives you better understanding of the issues if you run into them.

    For example, in bash you can test like this:


    smtp_user="ocid1.user.oc1..******@ocid1.tenancy.oc1..********.ql.com"
    smtp_password="******"
    smtp_host=smtp.email.us-ashburn-1.oci.oraclecloud.com
    smtp_port=587
    sender="***********"
    rcpt="*********"
    mail_file=mail.txt

    cd /tmp
    cat <$mail_file
    From: "User Name" <${sender}>
    To: "Tester" <${rcpt}>
    Subject: This is a test

    Hi Tester,
    I'm sending this mail with curl thru my OCI account
    Bye!
    EOF

    curl -v "smtp://${smtp_host}:${smtp_port}" --ssl-reqd -u "${smtp_user}:${smtp_password}" --mail-from "${sender}" --mail-rcpt "${rcpt}" --upload-file $mail_file






    ReplyDelete
  34. Hi, this is very weird. Using curl does work, but with apex don't

    ReplyDelete