send emails using UTL_MAIL Oracle package and Google GMAIL SMTP Server

This setting is probably not what you going to use in your organization, I use this in my home. My guess is the usage in your case would be somewhat similar.

As you probably know in Oracle Database 10g and above you can send email using UTL_MAIL (10.1 had tons of bugs, I would not recommend UTL_MAIL prior 10.2). Older releases of Oracle can use UTL_SMTP – I still use it if I want to attach large files, you can also use custom JAVA stored procedure.

Let me focus on UTL_MAIL here…. it’s really easy to use. This package is not installed by default. First you need to install it (see oracle documentation for detail how-to), the only parameter you will need to set is SMTP_OUT_SERVER which is basically SMTP host name and port (usually 25).

Simple huh? Well yes if you have internal SMTP server and that server does not require authentication. If you are like me then you probably don’t run SMTP server and would prefer to use Google GMAIL SMTP server. So is there a way to use UTL_MAIL with GMAIL SMTP?

Simple answer is NO, but there is simple workaround.

What you have to do is to run SMTP Relay, which is basically tiny app which routes any SMTP traffic to other SMTP server. As you probably guess this SMTP relay is going to run on your internal server, it will accept any SMTP traffic (including traffic from your Oracle server) with no password restrictions and then authenticate and relay this traffic to GMAIL SMTP Server.

There are quite of lot of SMTP Relay servers, I personally really like free E-MailRelay because it runs on Linux, Unix and Windows and source code is freely available.

note: I’m going to run E-MailRelay on the same server where Oracle resides (Oracle 10.2 on RedHat 5 in VMWARE). Since this is home setting, it should be OK. I also tested in on Windows 2003 with Oracle XE running in VMWARE.

1. download E-MailRelay for your system and extract it from the archive

2. install it

here is how to install it for Windows
The install program basically puts all files in one folder, creates script (BAT file) as well as emailrelay.auth file. The easies way is to replace emailrelay.auth file with (3) and edit BAT file and replace the content with (4) – just make sure the paths are correct.

on linux look at file called “install” – it has very detail easy to follow instructions (basically run configure, then make, then make install)

3. create secret file (this is simple file which includes your gmail UID/PSW – assuming you already have your gmail email setup), I call it emailrelay.auth and save it to folder /etc

login client myname@gmail.com mypassword

4. run emailrelay

emailrelay --as-proxy smtp.gmail.com:587 --client-tls --client-auth /etc/emailrelay.auth

5. install and configure UTL_MAIL

open SQL plus, login as sys and execute 3 statements below, change ip-address:port to your IP address, keep port on 25, if you run it on the same server where oracle sits, you can use localhost instead of IP address.

@$ORACLE_HOME/rdbms/admin/utlmail.sql;
@$ORACLE_HOME/rdbms/admin/prvtmail.plb;
alter system set smtp_out_server = 'ip-address:port' scope=Both;

6. send you first email

UTL_MAIL.send(sender     => 'My Name "<myname@gmail.com>"',
              recipients => 'myname@gmail.com',
              subject    => 'Test',
              message    => 'It works!');

Please make sure sender is exactly same format as above (keep the double-quotes around the email address), otherwise you will get error SMTP permanent error: 553 mailbox name not allowed: missing or invalid angle brackets in mailbox name

It cannot be easier than that. Small note: if your organization requires SMTP authentication and you cannot use SMTP Relay trick, then you can use UTL_SMTP for your email communication (UTL_SMTP does support authentication).

About these ads