Sending content-type: html e-mail from an Oracle database with utl_smtp
Blog Home



Recent Blog Posts
Show All Posts

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.

Code Syntax to send Content Type HTML emails using Oracle database:

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

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',
                  'Test Email!');
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);
From www.builderau.com.au
Posted by Rajiv on Saturday, February 21, 2009 at 01:56-PM under SQL Serever / Oracle / PLSQL
Post Comments (0) Back to Top  



© Copyright 2009 All rights reserved, Rajiv Sharma
Home | Contact Me | Sitemap | Privacy Notice & Disclaimer
Visitor's IP Address:
107.21.136.116