How can I configure Gmail with Oracle Apex using stunnel (application express) 5.0.3

Question

How can I setup Oracle Apex (application express) to send emails through my Gmail account without using my own SMTP ?

Answer 1

Background :

• Apex 5.0.3 is installed with Oracle XE 11.2 installed on Centos (linux) 6.x
• SMTP is not required to be enabled on centos. We will use gmail one directly.
• Solution is based on windows implementation published by : HÅVARD KRISTIANSEN at : http://monkeyonoracle.blogspot.com/2009/11/plsql-and-gmail-or-utlsmtp-with-ssl.html
• Will use stunnel to communicate with gmail directly.
• Apex / Oracle XE installation instructions are not included.

Installation (written out of my head) :

1.Install stunnel as root :

yum install stunnel -y

2.create a conf file for stunnel using nano or vi (to install nano : yum install nano -y )

nano /etc/stunnel/stunnel.conf

3.Enter the following to new stunnel.conf created:

; Use it for client mode
client = yes

[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465

4.restart stunnel using whatever method, e.g kill -9 and start using the following command :

/usr/bin/stunnel

5.Log in to apex as admin : http://yourhost:port/ords/apex_admin

Go to : Manage instance -> instance settings.

6.Put the following settings :

SMTP Host Address : 127.0.0.1 (or your local)
SMTP Host Port : 1925 (as u can see in stunnel.conf above).
SMTP Authentication Username : your gmail username : etai.guday (WITHOUT @gmail.com)
SMTP Authentication Password : gmail password
Use SSL/TLS : NO
Default Email From Address : etai.guday@gmail.com (including @gmail.com)

7.Due to gmail restrictions YOU MUST enable : https://g.co/allowaccess to use the above method (with relevant gmail account).

8.Enable Oracle DB ACL by using example bellow

https://www.dropbox.com/s/2ieaawy5gme9a50/email_configuration_acl_for_apex.txt?dl=0

All above should work :-) didn't have time to test it further or refer to security issues etc