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.

9 comments
shahmat
shahmat

Hi everyone, I have created the stores procedured, and configure them, but when I execute, I got the next error: Error report: ORA-29278: SMTP transient error: 421 Service not available ORA-06512: at "SYS.UTL_SMTP", line 17 ORA-06512: at "SYS.UTL_SMTP", line 96 ORA-06512: at "SYS.UTL_SMTP", line 374 ORA-06512: at "CEOADMIN.SP_ENVIARCORREOALERTASVENCIDAS", line 28 ORA-29279: SMTP permanent error: 530 5.7.0 Must issue a STARTTLS command first. 13sm4958418gxk.5 ORA-06512: at line 14 29278. 00000 - "SMTP transient error: %s" *Cause: A SMTP transient error occurred. *Action: Correct the error and retry the SMTP operation.

ahmed_wohishi
ahmed_wohishi

How can you set the smtp server to work propably with above code ?

KJCook
KJCook

I am in the same boat as you. We just upgraded our database to 10g and I don't have time to change my email program. However, I have found that if my email is longer than 1000 characters the procedure fails with the following error message: "ORA-29278: SMTP transient error: 421 Service not available" at the line of code: "utl_smtp.close_data(l_connection);". As long as I keep my email under a 1000 charcaters I am OK. I am sending out an html email. I have a varaible defined as a clob and I load it with everything I need. I then try and write it out in chunks of a 100.

prashantkr.83
prashantkr.83

Hey Justin, Its a good topic that u have taken for discussion.. Actually i was looking for an article on this matter.. For me the scenario is: i have Oracle 10g installed on my laptop and i wish to develop an application capable of sending emails automatically depending on some conditions. What are things that needs to be installed.... and how can i move forward in this direction... please suggest regards, Prashant

Justin James
Justin James

Great job with the abstraction Oracle! The only thing they didn't do was have you open a raw TCP/IP socket yourself, outside of that, it is just like writing a very basic MTA, right down to the "helo" command. And I thought having to do a file open on "| sendmail -t" was a pain in the neck, back in the old Unix days... J.Ja

RexWorld
RexWorld

They did introduce with 10g a more modern utl_mail package. Haven't had to crack it open personally yet, but from what I've read it is a much higher-level abstraction. None of the low-level HELO and all that junk. But you're right, even back with 8i they should have been able to give a better API for email. It is consistent with how they do other things, like if you've ever used their dbms_ldap to query an ldap server. It is downright painful how much code you have to write to make it work. They just seem to really favor thin API's in their helper packages like this. Very low-level and close to the raw code.

Justin James
Justin James

SMTP is not one of those situations where there can be an upside to primitive access. It is not like pointers in C vs. unmanaged code, where the pain of points is offset by the potential for efficiency. Low level access to SMTP accomplishes precisely nothing. I just view this as another example of the overarching laziness and lack of attention to detail on Oracle's part. Especially since, as your sample code demonstrates, it only takes a bit of work to abstract out that utl_smtp package to something actually useful. J.Ja

psmith
psmith

While it may offer nothing of value to you, it most certainly does offer utility from some perspectives (i.e. conditional logic at lower levels). From that perspective it can have great utility. From outside, it's simply a burden. Go Gamecocks!

Editor's Picks