Tech

Database Management TechGuides

Sending e-mail from an Oracle database

Learn how you can use the nice modern utl_mail package to send e-mail messages from your PL/SQL applications.

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.

Latest from Blog Central

Up close and personal with a merger

Read more »

What's important from our sponsors

Business

Intelligence Summit

 

Cutting-edge technology from premium sponsors

HP Simply StorageWorks D2D Backup System

Explore this disk-to-disk backup system that is fully automated and provides reliable data protection for your business.