Tuesday, November 3, 2009

PL/SQL and Gmail (or UTL_SMTP with SSL)

In this post I will describe how to send mail from an Oracle database using UTL_SMTP over SSL using Stunnel. I conducted the test on Windows XP with Oracle database 11gR1, but it should work for nix-operating systems and database versions 9.2 and up. To be quite frank, this is nothing new, but it might be of use anyway.

Preface
I wanted to send emails from my database when some data changes. It was not a corporate solution with access to an internal smtp-host. A simple, accessible, ISP agnostic smtp-server would do. In my case, Gmail fitted the bill, only problem was that Gmail required SSL, which UTL_SMTP does not support. I am up for a challenge (meaning: I am good at complicating (ing, not ed :-)) things), so here goes...

Stunnel
Since UTL_SMTP does not support SSL, I will use a third party tool to "wrap" my connection. There are probably any number of tools which can do this, but Stunnel is quite often referred to, and very easy to install and configure. For nix systems, I suggest checking the Examples-page on stunnel.org, this is a Windows-specific explanation. This part of the post is based on a thread on ez.no.

Installing and configuring Stunnel
  • Go to stunnel.org and download the latest Windows binaries
  • Install Stunnel (take note of the installation path), in my example it is c:\stunnel
  • Edit the file stunnel.conf located in installation folder to (just backup the original, and replace all the original text with the text below):
; Use it for client mode
client = yes

[ssmtp]
accept  = 1925
connect = smtp.gmail.com:465
Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com.

Start Stunnel.exe, and test the configuration:
  • Start cmd
  • Write: telnet localhost 1925
  • You should then see something like "220 mx.google.com ESMTP 5sm18031572eyh.34"
  • Write: quit

Troubleshooting: If you cannot reach smtp.gmail.com, there can be any number of things gone wrong.
  • Try a normal ping to smtp.gmail.com
  • Check to see if stunnel.exe is excepted properly in all firewalls (Windows native and other software firewalls)

Once stunnel is working, and if you are familiar with UTL_SMTP, don't bother reading on. This is the same as UTL_SMTP with any other smtp-host requiring authentication.

Setting up ACL (11g only)
This is more or less monkeyed from Arup Nandas 11g series.

To create an access control list for your application user, and enabling it to connect to localhost on port 1925, do the following:
-- create acl
begin
        dbms_network_acl_admin.create_acl (
                acl             => 'gmail.xml',
                description     => 'Normal Access',
                principal       => 'CONNECT',
                is_grant        => TRUE,
                privilege       => 'connect',
                start_date      => null,
                end_date        => null
        );
end;
/
-- add priviliege to acl
begin
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
end;
/
-- assign host, port to acl
begin
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
end;
/
And you are ready to use UTL_SMTP against smtp.gmail.com.

Wrapping UTL_SMTP
I have created a small test-package based on the old UTL_MAIL example from Oracle. Your schema user must have execute privileges on UTL_SMTP and UTL_ENCODE for this to work:
create or replace package apex_mail_p
is
   g_smtp_host      varchar2 (256)     := 'localhost';
   g_smtp_port      pls_integer        := 1925;
   g_smtp_domain    varchar2 (256)     := 'gmail.com';
   g_mailer_id constant varchar2 (256) := 'Mailer by Oracle UTL_SMTP';
   -- send mail using UTL_SMTP
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   );
end;
/
create or replace package body apex_mail_p
is
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
   )
   is
   begin
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
      );
   end;
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
   )
   is
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
   begin
      -- get characterset
      select value
      into   nls_charset
      from   nls_database_parameters
      where  parameter = 'NLS_CHARACTERSET';
      -- establish connection and autheticate
      l_conn   := utl_smtp.open_connection (g_smtp_host, g_smtp_port);
      utl_smtp.ehlo(l_conn, g_smtp_domain);  
      utl_smtp.command(l_conn, 'auth login');
      utl_smtp.command(l_conn,utl_encode.text_encode('<your gmail account including @gmail.com>', nls_charset, 1));
      utl_smtp.command(l_conn, utl_encode.text_encode('<your gmail account password>', nls_charset, 1));
      -- set from/recipient
      utl_smtp.command(l_conn, 'MAIL FROM: <'||p_sender||'>');
      utl_smtp.command(l_conn, 'RCPT TO: <'||p_recipient||'>');
      -- write mime headers
      utl_smtp.open_data (l_conn);
      write_mime_header (l_conn, 'From', p_sender);
      write_mime_header (l_conn, 'To', p_recipient);
      write_mime_header (l_conn, 'Subject', p_subject);
      write_mime_header (l_conn, 'Content-Type', 'text/plain');
      write_mime_header (l_conn, 'X-Mailer', g_mailer_id);
      utl_smtp.write_data (l_conn, utl_tcp.crlf);
      -- write message body
      utl_smtp.write_data (l_conn, p_message);
      utl_smtp.close_data (l_conn);
      -- end connection
      utl_smtp.quit (l_conn);
   exception
      when others
      then
         begin
           utl_smtp.quit(l_conn);
         exception
           when others then
             null;
         end;
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
   end;
end;
/
This is NOT production-ready code: First of all, you do not want your credentials in the open, at least obfuscate the package body.

Some notes on the package:
  • Parameters sender and recipient must contain e-mail addresses only, use the get_address function in the original Oracle example for more sophisticated use (you can also look at how to add attachments if you have the need).
  • I had some trouble encoding my account name and password. My initial thought was to use utl_raw.cast_to_raw and utl_encode.base64_encode, but this did not work, so I ended up using utl_encode.encode_text
  • Mime-type is set to "text/plain", set it to "text-html; charset=<something appropriate>" to enhance visual layout

Sending an E-mail
To test it all, try:
begin
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
end;
/
And you are done!

Well, if you don't get any error messages, that is. If you encounter any exceptions, first of all check your Gmail credentials. Next, check where (in the PL/SQL-code) it fails, and use your favorite search engine to do a combined search on smtp command sent and smtp-error received. Chances are others have worked through the same problems (even if they are not Oracle-related). Last resort is to use telnet and manually type the commands, a bit cumbersome but gives full control.

Happy coding :-)

65 comments:

  1. Thank you for the post. I'm quite new to this area and the post talked about how to config and send emails.
    So I would really appreciate if you could please also explain a little bit about how to config and retrieve emails by using Stunnel. Thanks.

    Br,
    Shichao

    ReplyDelete
  2. @Shichao

    Polling for mail is a whole other ball game, and I have no experience with Oracle database as a recipient.

    That being said, it should be possible using Blat. Check out http://weblogs.asp.net/nleghari/articles/gmailbackup.aspx or blat.net for details.

    ReplyDelete
  3. Hi,

    I did upto your post "Here I use port 1925 on my localhost (unused as far as I know) to connect to smtp.gmail.com."

    Also I started Stunnel.exe but I don't know where to perform your test configuration.

    Where to write "telnet local 1925", I tried under my cmd (command prompt) but it's giving me error as "telnet is not recognized ........."

    So can you elborate little bit more where to perform test configuaration so that I can recive the message as "220 mx.google.com ESMTP 5sm18031572eyh.34"

    Thank, I appreciate your reply

    ReplyDelete
  4. @Deep

    Looks like you are missing the telnet client. You did not specify, but I'm guessing you run MS Windows Vista.

    As far as I know, telnet is installed default with every version of Windows XP (which I used in this example). Not so with Vista. Check out http://windowsitpro.com/article/articleid/93952/where-is-the-telnet-client-in-windows-vista.html on how to enable telnet i Vista.

    With telnet enabled you should be able to write "telnet localhost 1925" in command prompt to establish a connection with gmail. locahost must be declared in your hosts-file, and stunnel must be running with modified config-file for this to work.

    Good luck :-)

    ReplyDelete
  5. Thank You. Hats off to you!!!!!

    That worked for me successfully.

    ReplyDelete
  6. Thank you so much... really... your post has really helped me a lot :).. I had to find the way to send mails fom Apex and thanks to you now I've found it :). Altougt I'm not sure if this will work, cuz maybe I will have to use an internal smtp-host but I've couldn't sent any mail form it, maybe I don't know how to configure the smtp :P... anyway, this might work, I just have a question... how to attach a file?...

    PD: Sorry for my bad ortography, English is not my natural language :P.

    ReplyDelete
  7. @snipercat

    Unless you have to use SSL for your internal mail server, I would very strongly advise you to check out the native mail support in APEX.

    See this tutorial to get more information. It is really easy to use, and no stunnel involved.

    If you have to use SSL and attach a file, you can either use UTL_MAIL (10g and above), or UTL_SMTP with mail demo wrapper package. Either way you have to use stunnel as described above to wrap your smtp-connection.

    Good luck :-)

    ReplyDelete
  8. You don't know how much you has helped me... Finally I've sent a mail using the SMTP from my University, I just had to modify a little your code... Thank you... Thank you so much... really, If I could to invite you to drink a beer or something you like, I would do it :P...

    Now that I've sent a mail, I will try to send a file with the help of the links you gave me :).. Although I've already used one, that helped me to send mails from Gmail using an Apex Interface :)...

    Again.. Thank you so much...

    ReplyDelete
  9. Has this support in windows 2003 server?

    ReplyDelete
  10. @jayavel:

    Define "support"!

    Will it be supported by Oracle: No
    Will it be supported by Microsoft: No
    Are there any commercial vendors supporting Stunnel: No

    Will it work: Most likely

    ReplyDelete
  11. Thank you so much... really... your post has really helped me a lot :).

    How to send multiple receipts?

    Good luck :-)

    ReplyDelete
  12. heloo mr, i try.. open ssl with stunel, but error
    ORA-20000: Failed to send mail due to the following error: ORA-29278: SMTP transient error: 421 Service not available
    ORA-29278: SMTP transient error: 421 Service not available
    ORA-06512: at "SYS.APEX_MAIL_P", line 68
    ORA-06512: at line 2
    how solution?? thanks

    ReplyDelete
  13. @Kue

    This you would typically get when you are unable to connect to gmail.com. This can be due to a number of reasons.

    If you can successfully execute the telnet command in the stunnel section of this post, then you are probably good to go.

    Good luck!

    ReplyDelete
    Replies
    1. I am stuck at the beginning. After successfully installing stunnel I am trying to replace the content of the stunnel.conf file with your lines and it won't let me. I am the only user on my laptop with vista. Any suggestions?

      Delete
  14. Mr havard , i have a problem with stunnel
    Configuration successful
    2010.10.10 16:31:13 LOG5[3108:1564]: Service ssmtp accepted connection from 127.0.0.1:1783
    2010.10.10 16:31:23 LOG3[3108:1564]: connect_blocking: s_poll_wait 209.85.227.109:465: timeout
    2010.10.10 16:31:23 LOG5[3108:1564]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

    ReplyDelete
  15. Had a nice time trying this out on Oracle 9.2.0.6 on RHEL4. Thanks for the post.

    My two cents:
    1. RHEL3 comes with stunnel installed. I just needed to create the stunnel.conf file in /etc/stunnel. Contents of the file is same as what you mentioned. Then start stunnel by executing the command 'stunnel &'
    2. utl_encode.text_encode is not available in 9.2.0.6. Used UTL_ENCODE.BASE64_ENCODE instead. ie, instead of utl_encode.text_encode('', nls_charset, 1) use UTL_SMTP.command(l_mail_conn, UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(''))));

    ReplyDelete
  16. Is this a working example?
    I am using Oracle XE.

    ReplyDelete
  17. i want to send email with attachment(image jpg/gif ) please guide me

    ReplyDelete
  18. hello i m really happy with this post,bt the issue i m having is that i dnt knw if it ll work with oracle 10g r2.i m new to this area n i want to implement it in our next project.
    thankz

    ReplyDelete
  19. Oracle 11gr2 (11.2.0.2) utl_smtp supports SSL built-in. It works great, we upgraded just to get this feature.

    ReplyDelete
  20. Thank you for sharing this.
    Rg
    Damir Vadas
    www.vadas.hr

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. Good.
    Someone could attach document with this routine,
    can you please explain how to do.
    Thank you.

    ReplyDelete
  23. Thanks a lot.........i tried first time and its work.
    really happy

    ReplyDelete
  24. can anybody plz tell me that i want to enter space while sending text message.using the abobe package
    what changes i have to do.....

    ReplyDelete
  25. great work man carry on with new task

    thanks alot

    ReplyDelete
  26. Thanks for your great post!
    From your post, you are using gmail account to send email.
    Can you guide on how to send mail using hotmail, yahoo or exchange server?

    Thank you in advance!

    ReplyDelete
  27. Excelente post, muy útil, gracias. Recuerden sustituir la cuenta de salida en el package

    ReplyDelete
  28. This is great, its working
    Thanks

    ReplyDelete
  29. No me funciona el ping a smtp.gmail.com
    Aquì esta el error:

    C:\Users\xxxxxxxxxx>ping smtp.gmail.com

    Haciendo ping a gmail-smtp-msa.l.google.com [74.125.134.109] con 32 bytes de dat
    os:
    Tiempo de espera agotado para esta solicitud.
    Tiempo de espera agotado para esta solicitud.
    Tiempo de espera agotado para esta solicitud.
    Tiempo de espera agotado para esta solicitud.

    Estadísticas de ping para 74.125.134.109:
    Paquetes: enviados = 4, recibidos = 0, perdidos = 4
    (100% perdidos),

    Cuando realizo el telnet localhost 1925, el stunnel informa lo siguiente:
    2012.10.12 16:01:12 LOG5[3728:4228]: Service [ssmtp] accepted connection from 127.0.0.1:49930
    2012.10.12 16:01:13 LOG3[3728:4228]: connect_blocking: connect 74.125.134.109:465: Connection refused (WSAECONNREFUSED) (10061)
    2012.10.12 16:01:14 LOG3[3728:4228]: connect_blocking: connect 74.125.134.108:465: Connection refused (WSAECONNREFUSED) (10061)
    2012.10.12 16:01:14 LOG3[3728:4228]: connect_blocking: connect 2607:f8b0:4002:c02::6d:465: Network is unreachable (WSAENETUNREACH) (10051)
    2012.10.12 16:01:14 LOG5[3728:4228]: Connection reset: 0 byte(s) sent to SSL, 0 byte(s) sent to socket

    Tengo el firewall deshabilitado, y cuando hago telnet smtp.gmail.com 587 me muestra lo siguiente:
    220 mx.google.com ESMTP i20sm7078623ank.17

    Alguien puede ayudarme????
    Saludos

    ReplyDelete
  30. Hi,

    I have an Windows XP desktop with Oracle XE 11g R2 installed, with APEX 4.0 version

    I´ve tried to test the command: telnet localhost 1925
    No answer appeared. After press to times button, i´ve depared with this error:
    SSL_accept: 1408F10B: error:1408F10B:SSL routines:SSL3_GET_RECORD:wrong version number
    I can ping without problem smtp.gmail.com server. I´ve desactivated windows firewall.
    What´s wrong?
    Best regards,
    Sergio Coutinho
    Brazil

    ReplyDelete
  31. Excelente artículo, lo probé y funcionó correctamente...
    los datos para el archivo stunnel.conf son:
    [ssmtp]
    client = yes
    accept = 1925
    connect = smtp.gmail.com:465

    Thanks OraMonkey

    ReplyDelete
  32. Thanks a lot. This helped me out a lot.

    ReplyDelete
  33. Hi
    thanks ur post helped me to achieve this..but can u pls tell hw can we send a attachment also along with this mail?

    ReplyDelete
  34. Hi Thanks for explain this. I am getting error at the time of establishing the connection
    "l_conn := utl_smtp.open_connection (g_smtp_host, g_smtp_port);"
    and its showing me the exception transient error "29278 ORA-29278: SMTP transient error: 421 Service not available". Please help me.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. It is because of some time slow internet connection.
      Just try again plz.

      Delete
  35. Thanks It is working fine..... Can we also send the attachment with it? If can then how?
    Please guide me. Thanks a lot

    ReplyDelete
  36. This comment has been removed by the author.

    ReplyDelete
  37. Thanks. Best source of knowledge about sending emails from oracle via gmail.

    ReplyDelete
  38. HI

    thanks. excelent post, i have a question, did you try it on any linux?

    ReplyDelete
  39. how to send email from scott user

    ReplyDelete
  40. I like this post and i feel very happy to read this article...
    More info:- Windows Live Mail Technical Support

    ReplyDelete
  41. Very Good Solution...its working fine.....excellent....10 out of 10......5 star............Thank you very much sir...........

    ReplyDelete
  42. When send one,two email - all fine.
    When send three or more message in oracle loop, then in stunnel log:
    remote socket: Address family not supported by protocol (97)
    Connection reset: 0 byte(s) sent to SSL, 0 byte(s) sent to socket

    I'm add in procedure mail: utl_tcp.close_all_connections;
    What am I doing wrong?

    ReplyDelete
  43. Hi nice post... it worked fine.. the next task for me is to send mail with attachments please help me to do that..

    ReplyDelete
  44. Your blog is really awesome and I got some useful information from your blog. This is really useful for me. Thanks for sharing such a informative blog. Keep posting.

    Regards..
    Cloud Computing Training

    ReplyDelete
  45. Hi ,

    Thanks a lot for sharing good solution. Can you also share code for reading mail box.

    Thanks and Regars,
    S Ranga Prasad

    ReplyDelete
  46. Thank you. This is working fine to me.
    Now, I want to inform You that I will use this in my apex application to send reports to my clients but the question is that how could I be able to receive mail from the clients on the same apex interface to know their requirements and what they actually want to see in the reports.
    Kindly help me out.
    Thank You.
    Regards,
    Saim Ahmed.

    ReplyDelete
  47. Thank you very much! Very usefull!

    ReplyDelete
  48. Very Nice Blog I like the way you explained these things.
    LOCAL BUSINESS DIRECTORY

    ReplyDelete
  49. GREEN WOMEN HOSTELGreen Women hostel is one of the leading Ladies hostel in Adyar and we serving an excellent service to Staying people, We create a home atmosphere, it is the best place for Working WomenOur hostel Surrounded around bus depot, hospital, atm, bank, medical Shop & 24 hours Security Facility



    ReplyDelete
  50. brilliant article that I was searching for. Helps me a lot
    call360 is Fastest local search Engine we have 12 years of experience in online industery, in our Search Engine we offer,
    more than 220 categories and 1 Million Business Listing most frequently search categories
    are Money exchange Chennai and Bike mechanic Chennai,
    we deliver 100% accure data to users & 100% Verified leads to our
    registered business vendors and our most popular categories are
    AC mechanic chennai,
    Advertising agencies chennai
    catering services chennai

    ReplyDelete
  51. brilliant article that I was searching for. Helps me a lot.
    We are one of the Finest ladies hostel near OMR and our
    womens hostel in adyar is secure place for working womens
    we provide home based food with hi quality, our hostel located very near to Adyar bus depot.
    womens hostel near Adyar bus depot, we are one of the best and experienced
    womens hostel near omr

    ReplyDelete

  52. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  53. Nice it seems to be good post... It will get readers engagement on the article since readers engagement plays an vital role in every blog.i am expecting more updated posts from your hands.
    Android App Development Company

    ReplyDelete
  54. I am getting the following error at the execution:
    ---
    ERROR at line 1:
    ORA-20000: Failed to send mail due to the following error: ORA-29279: SMTP
    permanent error: 535 5.7.8 https://support.google.com/mail/?p=BadCredentials
    e31sm2776593wre.54 - gsmtp
    ---
    I am 100% sure of my gmail username and password.

    ReplyDelete
  55. I wondered upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I’ll be subscribing to your feed and I hope you post again soon.
    iOS App Development Company
    iOS App Development Company

    ReplyDelete
  56. I am expecting more interesting topics from you. And this was nice content and definitely it will be useful for many people.
    Fitness SMS
    Fitness Text
    Salon SMS
    Salon Text
    Investor Relation SMS
    Investor Relation Text
    Mobile Marketing Services
    mobile marketing companies
    Sms API

    ReplyDelete
  57. how can i do it in linux server with oracle db 11gr2

    ReplyDelete
  58. Thanks a lot, after many hours dealing with this, hopely works. Thanks again.

    ReplyDelete
  59. You have provided an nice article, Thank you very much for this one. And i hope this will be useful for many people.. and i am waiting for your next post keep on updating these kinds of knowledgeable things...
    Texting API
    Text message marketing
    Digital Mobile Marketing
    Mobile Marketing Services
    Mobile marketing companies
    Fitness SMS

    ReplyDelete
  60. Thank you for sharing this information. This article is very interesting and useful. Keep up the good work!



    Melbourne SEO Service

    ReplyDelete
  61. This article is very much helpful and i hope this will be an useful information for the needed one. Keep on updating these kinds of informative things...
    Sms marketing
    Text message marketing
    Fitness SMS

    ReplyDelete