Sending Email from the Database

The Problem

We needed to send email alerts from several of our applications. APEX (Oracle Application Express) has email functionality built into it, but not all our applications are built in APEX.

We also wished to send calendar appointments. I've documented how to send appointments as an article in it's own right.

The Solution for Email

We created a database package, lets call it MAIL_PKG (we didn't, but perhaps should have). It has two procedures: SEND_EMAIL and SEND_HTML_EMAIL. The first is used to send a simple text email while the second sends, you guessed it, a message with a html body. This is what the package header looked like:

create or replace package mail_pkg as
 
-------------------------------------------------------------------------------
-- SEND_MAIL is used to send a simple text email
  procedure send_mail
  (
      msg_to      in varchar2
    , msg_from    in varchar2
    , msg_subject in varchar2
    , msg_text    in varchar2  
  );
 
-------------------------------------------------------------------------------
-- SEND_HTML_MAIL is used to send a html email
  procedure send_html_mail
  (
      msg_to      in varchar2
    , msg_from    in varchar2
    , msg_subject in varchar2
    , msg_text    in varchar2
    , msg_html    in varchar2
  );

end snh_mail;

The body of the SEND_MAIL procedure is:

procedure send_mail
(
    msg_to      in varchar2
  , msg_from    in varchar2
  , msg_subject in varchar2
  , msg_text    in varchar2  
)
as
 
  c  utl_smtp.connection;
  mailhost    VARCHAR2(30) := 'the.mail.server';
 
begin
 
  c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
  utl_smtp.helo(c, mailhost);
  utl_smtp.mail(c, msg_from);
  utl_smtp.rcpt(c, msg_to);
 
  utl_smtp.data(c, 'From: '    || msg_from     || utl_tcp.crlf ||
                   'To: '      || msg_to       || utl_tcp.crlf ||
                   'Subject: ' || msg_subject  || utl_tcp.crlf ||
                   ''          || utl_tcp.crlf || msg_text);
 
  utl_smtp.quit(c);
 
EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
     dbms_output.put_line(' Invalid Operation in Mail attempt using UTL_SMTP.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
     dbms_output.put_line(' Temporary e-mail issue - try again'); 
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
     dbms_output.put_line(' Permanent Error Encountered.'); 
 
end send_mail;

This procedure makes use of the UTL_SMTP package. There are two local variables: c for the connection to the mail server, and mailhost to point to the server. You can either enter this as a domain name or and IP address.

The first thing you need to do is open a connection to the mail server and then you can start sending it the information needed to assemble the email. The key part is the call to UTL_SMTP.DATA, where you send the content of the email. It's important to use the UTL_TCP.CRLF value for the carriage return / line feed pair.

That's it - it's quite simple really. The exception handling is there but we've never seen any exceptions raised yet (although I'm tempting fate now).

Sending HTML Emails

To send an email containing formatted html you need to do a little more work. Email was originally built as a simple text protocol so the standards around email have evolved within these restrictions. Here's the code

procedure send_html_mail
(
      msg_to      in varchar2
    , msg_from    in varchar2
    , msg_subject in varchar2
    , msg_text    in varchar2
    , msg_html    in varchar2
)
as
 
  c  utl_smtp.connection;
  mailhost    VARCHAR2(30) := 'the.mail.server';
  l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
 
begin
 
  c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
  utl_smtp.helo(c, mailhost);
  utl_smtp.mail(c, msg_from);
  utl_smtp.rcpt(c, msg_to);
 
  utl_smtp.data(c, 'From: '    || msg_from     || utl_tcp.crlf ||
                   'To: '      || msg_to       || utl_tcp.crlf ||
                   'Subject: ' || msg_subject  || utl_tcp.crlf ||
                   'Content-Type: multipart/alternative; boundary=' ||
                     chr(34) || l_boundary ||  chr(34) || utl_tcp.crlf ||
                   '--' || l_boundary || utl_tcp.crlf ||
                   'content-type: text/plain; charset=us-ascii' ||
                   utl_tcp.crlf || utl_tcp.crlf ||
                   ''           || msg_text ||
                   utl_tcp.crlf || utl_tcp.crlf ||
                   '--' || l_boundary || utl_tcp.crlf ||
                   'content-type: text/html;' ||
                   utl_tcp.crlf || utl_tcp.crlf ||
                   ''           || msg_html || utl_tcp.crlf ||
                   '--' ||  l_boundary || '--' || utl_tcp.crlf);
 
  utl_smtp.quit(c);
 
EXCEPTION
  WHEN UTL_SMTP.INVALID_OPERATION THEN
     dbms_output.put_line(' Invalid Operation in Mail attempt    
                            using UTL_SMTP.');
  WHEN UTL_SMTP.TRANSIENT_ERROR THEN
     dbms_output.put_line(' Temporary e-mail issue - try again'); 
  WHEN UTL_SMTP.PERMANENT_ERROR THEN
     dbms_output.put_line(' Permanent Error Encountered.'); 
 
end send_html_mail;
 

The first difference is the addition of the Content-Type instruction. This is a MIME command (Multipart Internet Mail Extensions if you really must know) that does just that - allows email to contain multiple sections with different content. The content is seperated using the boundary variable. The first part is the plain text for those who don't use html browsers whilst the second part contains the nicely formatted html version.

I've also documented the procedure to send an appointment.