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

25 thoughts on “send emails using UTL_MAIL Oracle package and Google GMAIL SMTP Server

  1. Luis Zevallos says:

    I have a problem

    ******************************************************
    ORA-29279: error permanente de SMTP: 553 mailbox name not allowed: missing or invalid angle brackets in mailbox name
    ORA-06512: en “SYS.UTL_SMTP”, línea 21
    ORA-06512: en “SYS.UTL_SMTP”, línea 99
    ORA-06512: en “SYS.UTL_SMTP”, línea 222
    ORA-06512: en “SYS.UTL_MAIL”, línea 407
    ORA-06512: en “SYS.UTL_MAIL”, línea 594
    ORA-06512: en línea 2

    View program sources of error stack?
    ******************************************************

    Please can you help me?

    thanks

  2. Luis Zevallos says:

    test e-mailrelay
    *************************************************
    [root@sv-dbd1 emailrelay-1.8.2]# telnet 10.10.10.210 25
    Trying 10.10.10.210…
    Connected to sv-dbd1.jmoran.com.pe (10.10.10.210).
    Escape character is ‘^]’.
    220 sv-dbd1.jmoran.com.pe — E-MailRelay V1.8.1 — Service ready
    EHLO gmail.com
    250-sv-dbd1.com.pe says hello
    250-VRFY
    250 8BITMIME
    mail from:
    250 OK
    rcpt to:
    250 OK
    data
    354 start mail input — end with .
    mensage de prueba
    .
    250 OK
    quit
    221 closing connection
    *****************************************
    All OK
    this send the message…

  3. jiri says:

    It’s I think kinda funky behavior of Oracle UTL_MAIL package, it basically strips angle brackets characters from the sender. You have to add double-quotes to sender name. See (6) above – I made small change to make it easier to read and understand.

    I tested it on Oracle XE on Windows 2003 in VMWARE with gmail SMTP and it works.

    hope it helps
    jiri

  4. Jeffrey Kemp says:

    Hi jiri,

    Thanks for this article.

    I’m trying to set this up on my Oracle server running on Amazon’s EC2, but when I try to start the emailrelay service I’m getting this error:

    $ emailrelay --as-proxy smtp.gmail.com:587 --client-tls --client-auth /etc/emailrelay.auth
    emailrelay: error: cannot bind the listening port: 0.0.0.0:25
    emailrelay: exception: cannot bind the listening port: 0.0.0.0:25
    

    Any ideas?

    Thanks, Jeff

  5. jiri says:

    My guess (and I am really not emailrelay pro) is that there is already other service running on port 25. Try to change emailrelay port e.g.


    --port 12345

  6. Jeffrey Kemp says:

    Thanks jiri, that’s right – it appears sendmail is listening on that port. I’ve tried an alternative port, but now I get a new error:

    $ emailrelay –as-proxy smtp.gmail.com:587 –client-tls –client-auth /etc/emailrelay.auth –port 8025
    emailrelay: error: cannot do tls/ssl: openssl not built in
    emailrelay: exception: cannot do tls/ssl: openssl not built in
    

    Any more ideas?

  7. jiri says:

    I’m not sure exactly how you installed it on amazon ec2, hopefully this will help

    1. make sure openssl-dev (OpenSSL Development Library) is installed

    2. run configure with this parameter (it should enable openssl). I would check config.log for openssl errors after you run configure


    ./configure --with-openssl

  8. Jeffrey Kemp says:

    Any idea where I can get openssl-dev? I’ve looked around but all I can find is openssl, which I’ve installed, but emailrelay still doesn’t find all the bits it needs.

  9. jiri says:

    it’s been a while I played around with this….

    I just installed FEDORA 13 in vmware and the development library is part of the packages you can install (not installed by default). I was able to start emailrelay with no problems (after I installed c++ packages and openssl-devel). Try this http://www.rpmfind.net/linux/rpm2html/search.php?query=openssl-devel hope it helps.

    I am really not linux guru, I think sendmail should be able to relay emails to gmail as well. I found this http://www.phinesolutions.com/sendmail-gmail-smtp-relay-howto.html

  10. Jeffrey Kemp says:

    Thanks for your help jiri. I’ve given up now – I tried openssl-devel but it comes up with:

    $ rpm -i openssl-devel-0.9.7a-2.i386.rpm
    warning: openssl-devel-0.9.7a-2.i386.rpm: Header V3 DSA signature: NOKEY, key ID db42a60e
    error: Failed dependencies:
    krb5-devel is needed by openssl-devel-0.9.7a-2.i386
    openssl = 0.9.7a-2 is needed by openssl-devel-0.9.7a-2.i386

    rpmfind doesn’t have krb5-devel for Red Hat.

    I tried the sendmail option, but my sendmail isn’t compiled with the necessary options.

    I’m finding it hard to understand why it’s such an uphill battle just to send emails from Amazon EC2.

  11. jiri says:

    I would probably do the same… sometimes it can be a bit painful

    if you want to keep going and win the battle, then

    krb5-devel is here http://www.rpmfind.net/linux/rpm2html/search.php?query=krb5-devel
    openssl is here http://www.rpmfind.net/linux/rpm2html/search.php?query=openssl&submit=Search+…&system=&arch=

  12. Jeffrey Kemp says:

    Thanks. I saw those, just nothing for my version of Linux.

    I’ve since found an easier way to send emails – via the ISP with basic authentication. It’s not as secure but it works.

  13. [...] недолгих поисков я нашел следующую статью http://jiri.wordpress.com/2010/03/24/send-emails-using-utl_mail-and-google-gmail-smtp-server/, описывающую как слать почту через GMAIL. Автор статьи [...]

  14. JIGNESH BHALANI says:

    Dear Sir,

    create or replace procedure test_mail as
    begin
    UTL_MAIL.SEND_attach_varchar2(sender => ‘jignesh@chaitanyaindia.in’,
    recipients => ‘Jignesh@chaitanyaindia.in’,
    cc => ‘Rakesh@chaitanyaindia.in’,
    bcc => ‘chaitanya_report@chaitanyaindia.in’,
    subject => ‘HIMARK REPORT’,
    message => ‘Auto Generated Mail’,
    attachment => ‘csv’,
    att_filename => ‘123.csv’);
    end;
    /
    This is working fine.

    I want to attach external file which is present in E:\JIG\123.csv

    Please guide me how to do it;

  15. jiri says:

    Check this forum

    https://forums.oracle.com/forums/thread.jspa?threadID=2210486&start=0&tstart=0

    if you have very large files, I would use unix email capability or just send a link to the file (if possible)

  16. Luis says:

    Jiri, I have this error, any ideas:

    C:\Emailrelay>C:\Emailrelay\emailrelay.exe –as-proxy smtp.gmail.com:587 –clien
    t-tls –client-auth C:\Emailrelay\emailrelay.auth
    emailrelay: warning: no valid domain in “LEP”: defaulting to “.local”

  17. jiri says:

    no clue

    are you behind firewall or proxy server?

  18. Hi Jiri, very helpful post.
    Im going crazy traing to send email with oracle and emailrelay.
    I tried everything, but Im still getting this error
    ORA-29279: error permanente de SMTP: 530 5.7.0 Must issue a STARTTLS command first. n37sm7195306anq.0

    Here is my test script ( Oracle 10)
    alter system set smtp_out_server = ‘smtp.gmail.com:587′;
    exec utl_mail.send(‘Nombre “”‘,’person@gmail.com’,’Subject’,’Mensaje’);

    And if I execute this:
    utl_smtp.command(conn,’STARTTLS’);

    I Recieve the error “Service Not aviable Negative Vibes”

    I’m stuck

    Thanks in advance

  19. Hi Jiri, thanks for this post.
    Very helpful.
    I follow the instructions, but i still gettin this error
    ORA-29279: error permanente de SMTP: 502 negative vibes
    I’m stuck
    Any ideas?
    Thanks

  20. jiri says:

    Are you behind proxy or firewall?

  21. Thanks so much for the article.Much thanks again. Will read on…

  22. Ramakrishnan says:

    sir i got the output as: execute UTL_MAIL.send(sender=>’Ramakrishnan “”‘,recipients=>’xxxx@yahoo.com’,subject=>’Test’,message=>’It works!’); PL/SQL procedure successfully completed.

    but not able to see the message in my inbox or spam or trash in my yahoo

  23. Ramakrishnan says:

    for the above i gave emailrelay –as-proxy smtp.gmail.com:587 –client-tls . if i give –client-auth /etc/emailrelay.auth it throws cannot read secret file…

    but i get procedure succeful…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: