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 :-)

46 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