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.

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...

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

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
        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
-- add priviliege to acl
  dbms_network_acl_admin.add_privilege ( 
  acl       => 'gmail.xml',
  principal    => '<YOUR SCHEMA USER>',
  is_grant    => TRUE, 
  privilege    => 'connect', 
  start_date    => null, 
  end_date    => null); 
-- assign host, port to acl
  dbms_network_acl_admin.assign_acl (
  acl => 'gmail.xml',
  host => 'localhost',
  lower_port => 1925,
  upper_port => 1925);
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
   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
create or replace package body apex_mail_p
   -- Write a MIME header
   procedure write_mime_header (
      p_conn in out nocopy utl_smtp.connection
    , p_name in varchar2
    , p_value in varchar2
      utl_smtp.write_data ( p_conn
                          , p_name || ': ' || p_value || utl_tcp.crlf
   procedure mail (
      p_sender in varchar2
    , p_recipient in varchar2
    , p_subject in varchar2
    , p_message in varchar2
      l_conn           utl_smtp.connection;
      nls_charset    varchar2(255);
      -- 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);
      when others
           when others then
         raise_application_error(-20000,'Failed to send mail due to the following error: ' || sqlerrm);   
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:
   apex_mail_p.mail('<your gmail address>', '<recipient address>', '<Subject>', '<message body>');
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 :-)


  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.


  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.

  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

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

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

    That worked for me successfully.

  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.

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

  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...

  9. Has this support in windows 2003 server?

  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

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

    How to send multiple receipts?

    Good luck :-)

  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

  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!

    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?

  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
    2010.10.10 16:31:23 LOG3[3108:1564]: connect_blocking: s_poll_wait timeout
    2010.10.10 16:31:23 LOG5[3108:1564]: Connection reset: 0 bytes sent to SSL, 0 bytes sent to socket

  15. Had a nice time trying this out on Oracle 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 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(''))));

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

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

  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.

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

  20. Thank you for sharing this.
    Damir Vadas

  21. This comment has been removed by the author.

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

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

  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.....

  25. great work man carry on with new task

    thanks alot

  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!

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

  28. This is great, its working

  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 [] con 32 bytes de dat
    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
    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
    2012.10.12 16:01:13 LOG3[3728:4228]: connect_blocking: connect Connection refused (WSAECONNREFUSED) (10061)
    2012.10.12 16:01:14 LOG3[3728:4228]: connect_blocking: connect 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????

  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

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

    Thanks OraMonkey

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

  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?

  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.

    1. This comment has been removed by the author.

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

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

  36. This comment has been removed by the author.

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

  38. HI

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

  39. how to send email from scott user

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

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

  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?

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

  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.

    Cloud Computing Training

  45. Hi ,

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

    Thanks and Regars,
    S Ranga Prasad

  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.
    Saim Ahmed.

  47. Thank you very much! Very usefull!

  48. Very Nice Blog I like the way you explained these things.

  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

  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

  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