id="info"

Software

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.

Editor's Picks