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