Enable Oracle database to send emails via SMTP server

Often in a complex enterprise Infrastructure Oracle DBAs face issues by enabling SMTP mail traffic on the databases through corporate email gateway servers. Imagine you have to provide your database applications an ability to send emails via Simple Mail Transfer Protocol (SMTP) protocol from Oracle database. Below I give a detail action plan to accomplish the same. My test example includes an Oracle database 11gR2 running on Linux RedHat 6 and a Microsoft Exchange corporate server.

1. Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP

Check if Oracle packages SYS.UTL_SMTP and SYS.UTL_TCP are available on Oracle database and you have EXECUTE grants on them.

2. Check SMTP access of database Linux server on mail server

– Check whether you are able to contact the email gateway server via SMTP from the database Linux box:

$ telnet smtp_server 25

If you see blank screen or an error: “telnet: Unable to connect to remote host: Connection refused”,
your DB server is not recognized by the SMTP server. In this case you have to apply for mail SMTP access.
Otherwise type the following commands to test sending email from Linux to your corporate email account:

helo
mail from: my_email@my_company.com # you should see "Sender OK'
rcpt to: my_email@my_company.com   # you should see "Recipient OK"
data                               # Start mail input
test email via SMTP and orcl DB
[Enter]
.                                  # mail should be sent
[Enter]
quit

3. Apply for mail SMTP access

Contact your mail (exchange) admins and apply for SMTP access on your corporate smtp mail gateway server. Below is an example: Read more »

DBMS Blog Updates : Subscribe RSS RSS: Subscribe to Articles · Subscribe to Comments Subscribe RSS Receive site updates via email