Sending e-mail from an Oracle database with utl_smtp

If you’re running Oracle 10g or later, you can use the nice modern utl_mail package to send e-mails from your PL/SQL applications. Even though the application I maintain is now running on Oracle 10g, it was built back on an Oracle 8i database, so it sends e-mails via the older (and more complicated) utl_smtp package. The nice thing about this code is that it runs fine on Oracle 10g, so we haven’t had any need to replace utl_smtp with utl_mail. I’m sure that day will come eventually, but for now, utl_smtp serves our needs.

To begin, the utl_smtp package has to be installed (in the SYS schema, naturally). If it wasn’t part of your install, you can find the utlsmtp.sql script in your ORACLE_HOME\RDBMS\admin directory. You’ll also need utl_tcp; again, if it’s not already loaded, the utltcp.sql script for that is in the same location as the utlsmtp.sql. Finally, you will need to know the URL for your corporate SMTP server. (Note: This example won’t work with secured SMTP servers like Gmail.)

The spec for our little e-mail package is fairly straightforward:

create or replace PACKAGE sendmail IS        

  procedure send (p_sender varchar2,
                  p_recipient varchar2,
                  p_subject varchar2,
                  p_body varchar2 default null);        

end sendmail;

For the body, you’ll notice that the public method send relies on a private method called common because I want to extend this package later and show how to send Binary Large OBject (blob) attachments. For example, if you’ve generated a PDF and stored it in your database, you might want to e-mail it as an attachment; the common method is in preparation for that. The code that will be used is from the basic send method and the send_blob method.

Here’s the package body:

create or replace PACKAGE BODY sendmail IS        

  procedure common (p_sender varchar2,
                    p_recipient varchar2,
                    p_subject varchar2,
                    c out utl_smtp.connection) is         

    v_recipient varchar2(1000);        

  begin        

    --make connection to smtp
    c := utl_smtp.open_connection('smtp.example.com');         

    --identify the domain of the sender
    utl_smtp.helo(c, 'example.com');        

    --start a mail, specify the sender
    utl_smtp.mail(c, p_sender);        

    --identify recipient
    utl_smtp.rcpt(c, v_recipient);        

    --start the mail body
    utl_smtp.open_data(c);        

    utl_smtp.write_data(c, 'From: ' || p_sender || utl_tcp.crlf);
    utl_smtp.write_data(c, 'To: ' || p_recipient || utl_tcp.crlf);
    utl_smtp.write_data(c, 'Subject: ' || p_subject || utl_tcp.crlf);        

  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit(c);
      raise;
    when others then
      raise;
  end common;        

  procedure send (p_sender varchar2,
                  p_recipient varchar2,
                  p_subject varchar2,
                  p_body varchar2 default null) is        

    c utl_smtp.connection;        

  begin        

    common(p_sender, p_recipient, p_subject, c);        

    utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);
    utl_smtp.write_data(c, utl_tcp.crlf || p_body);
    utl_smtp.close_data(c);
    utl_smtp.quit(c);        

  exception
    when utl_smtp.transient_error or utl_smtp.permanent_error then
      utl_smtp.quit(c);
      raise;
    when others then
      raise;
  end send;        

end sendmail;

There are a couple of places above where you’ll need to substitute your specific information. First is the line where I provide the SMTP server:

    --make connection to smtp
    c := utl_smtp.open_connection('smtp.example.com');

That’s where you’ll want to put whatever your corporate SMTP server happens to be. And the second place is where you identify your domain:

    --identify the domain of the sender
    utl_smtp.helo(c, 'example.com');

Again, replace that with whatever your domain really is. That’s all you’ll need to get the basic e-mail functionality working.

To call this, you’d use something like:

begin
  sendmail.send ('sender@example.com',
                  'recipient@example.com',
                  'Subject: Testing',
                  'Howdy!');
end;

You’ll notice that the body string I gave above has HTML embedded in it. This is because, in the send method, I set the content type to be text/html:

    utl_smtp.write_data(c, 'Content-Type: text/html' || utl_tcp.crlf);

Don’t be like my coworker who spent hours trying to send out formatted e-mails with chr(10) embedded as the linebreak character. It won’t work — HTML ignores that. You’ll need to use break or paragraph tags to format the message body.

Next time, we’ll send blobs as attachments.