Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question.

Asked: October 25, 2001 - 8:04 pm UTC

Last updated: March 02, 2021 - 1:47 am UTC

Version: 8i

Viewed 100K+ times! This question is

You Asked

Hi Tom

I hope I'm phrasing this correctly...

I'd like to be able to send an HTML formatted email from the database using the UTL_SMTP package. I don't see any way of setting the MIME type.

Is this beyond the scope of UTL_SMTP?

thanks in advance!

Ken


and Tom said...

I asked Tyler Muth (tyler.muth@oracle.com) to answer this one since I knew he already had the code (we use it on our Oracle Calendar software shipped with the email server to send reminders for appointments). Tyler provided this answer for us:

...

You are correct in you observation that there is no MIME type parameter in
UTL_SMTP, but this does not limit the types of email you can send. It's
actually very flexible, in that UTL_SMTP allows you to contruct and pass in
the whole email message. What this means to you is that constructing an
HTML message is possible using UTL_SMTP, but your going to have to do some
work to create it.

The folowing is the basic structure of the message you need to construct:


Normal Headers
Content-Type: multipart/alternative;
boundary="some_unique_string_not_in_email"

--some_unique_string_not_in_email
Content-Type: text/plain; charset=us-ascii

Text email message here.

--some_unique_string_not_in_email
Content-Type: text/html; charset=us-ascii

<html>
<head>
<title>some subject</title>
</head>
<body>
Your Html email message here.
</body>
</html>

--some_unique_string_not_in_email--


Ok, that looks hard, but if you use this procedure I wrote, its really quite easy, it does all of the work for you:

create or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
end;
/
show errors





Rating

  (137 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to

A reader, October 29, 2001 - 10:43 pm UTC

Hi, Tom,

How to let the db recognize the 'UTL_SMTP.CONNECTION',
which procedure we should run before using UTL_STNP?

Thanks

Tom Kyte
October 30, 2001 - 7:33 am UTC

I don't understand this follow up at all. What do you mean by "recognize". If you have 816, you have UTL_SMTP. You need to have java installed and configured on the server

See also
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:958229976043 <code>
...

A reader, October 30, 2001 - 7:52 am UTC

when i receive an email from tom i can't find any digital signuture also when i receive some information from sales pepole i get the same is that mean oracle mail havn't any signuture


Tom Kyte
October 30, 2001 - 8:50 am UTC

so?

Hang up

A reader, October 30, 2001 - 10:15 am UTC

Hi, Tom,

When i execute the program, why is it always hangup?

I created the procedure without any error.
VER: 8.1.6

Thanks

Plain text doesn't show...

jan van mourik, October 30, 2001 - 11:04 am UTC

Procedure works great! Only on my email, I don't see the plain text show up (p_text). Only the html test comes out (p_html). Is that because the mail client (both Lotus Notes and Yahoo! mail) can't handle it?

jan

Tom Kyte
October 30, 2001 - 1:18 pm UTC

The email is sent with multiple representations of the email. The email client gets BOTH and picks which one it wants to use. You could actually use this technique to send an email that has the text of the email in

o plaintext
o html
o rtf
o word
o whatever

and the email client will look at all of the representations to see what it wants to use, which it is best at rendereing.

you'll need to find a text based emailer to see what the text is looking like. Its getting sent though!

Now for attachments too...

Andrew, October 30, 2001 - 1:09 pm UTC

I assume attachments are done in a similar manner. An example would be great if you have one.

re: Now for attachments too...

A reader, October 30, 2001 - 1:23 pm UTC

If you click on "Search/Archives" in the upper right hand corner of the home page, type in "email attachment" in the "Search" text box and click on "Go!" you might be surprised what you can find.

Sending HTML using UTL_SMTP", version 8i

Casey Waters, October 30, 2001 - 6:11 pm UTC

You guys a great! Thanks!

Way to go!!

patrick, November 02, 2001 - 5:03 pm UTC

Way to go on this one!! after a fight with
initjvm...etc i got this to work great!!
thanks

Outstanding, thanks!

Bob Yexley, March 14, 2002 - 10:37 am UTC

I've implemented this, and it is working GREAT! Thank you so much for your help. I am having some strange behavior with it though, that I was hoping that you might be able to shed some light on. When I send the e-mail (I've sent it to myself at least a dozen times now) I've noticed that somehow the procedure randomly inserts exclamation points (!) in the message, and I have no idea why or where they're coming from. They're not in every message, and when they are in a message, they're never in the same place. It's completely random as far as I can tell. Have you ever seen this behavior before? If so, is there an explanation and possibly a fix for it? Any help would be greatly appreciated.

Thanks again.

Tom Kyte
March 14, 2002 - 10:51 am UTC

The !'s -- are they in the HTML or plaintext copy?

We use this internally at Oracle to send thousands of emails every day, haven't had a single report of errant !'s in it.

versions, os's and such would be helpful. An example of an email call that caused this to happen would be as well.

Followup to your followup

Bob Yexley, March 14, 2002 - 11:49 am UTC

The !s are showing up in the HTML formatted messages. I haven't even actually used this to send plaintext, HTML is preferred. We are running this from an 8.1.7.0.0 database on Solaris, the client workstations are Windows 2000 (SP2) with Outlook 2000 as the e-mail client.

I'd be happy to send you a copy of the code that calls this procedure, but there is not enough space allowed in this forum. I would gladly send you an e-mail though.

I may be beginning to notice where the problems is coming in though, but I'm still debugging it. I have just put all of the text of my message on one line in the code where I build the HTML message, and I've noticed that when I view the source of the HTML, the !s appear where there has somehow been line breaks inserted in the HTML. I don't know if that made sense or not, but what I mean by it, is that when you view the source of the HTML, at the end of every line where the browser has somehow forced a linebreak (I didn't insert the line break with <br> tags or anything), there appears the !s. If you need more clarity I can send it to you. Thanks for the fast response.

Tom Kyte
March 14, 2002 - 12:28 pm UTC

Sounds like it is OUTLOOK doing this then, not on our end. Don't know what to say on this one, we don't insert linebreaks, outlooks does that part.

sorry -- can you try it with a different email client?

Last followup

Bob Yexley, March 14, 2002 - 12:44 pm UTC

I probably could try it with a different mail client, but it wouldn't really do me much good. Outlook is the client that our customers are mandated to use, so I'm going to have to figure out a way to make it work with outlook. Thanks though...I'm 99% of the way there. :)

Spurious characters inserted into message body

Mark Russell-Brown, May 06, 2002 - 5:04 am UTC

Hello Tom,

A reviewer had complained that the html message had "!" characters apparently inserted in the text randomly. I had the identical problem and it seems due to the fact that the message text contains unprintable characters which are translated by the client email tool in this way.

I run the following test using your Oracle colleague's smtp package plus your wonderful utl_dir package (to get a file of the html that is generated).

It retrieves the "machine" column from v$session .. apparently this column contains a trailing unprintable character when the data refers to an NT domain logon.

Is there any way to fix this other than change the query to remove the trailing unprintable character using for example:

DECODE(INSTR(Machine,'\'),0,Machine,SUBSTR(Machine,1,LENGTH(Machine)-1))

I'm wondering if there's any function or trick to remove "unprintable" characters should these be present in a table column we wish to use in an html email.

Many thanks ,
Mark.
--------------------------------
declare
l_body varchar2(32767);
f filetype;
begin
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''DD-MON-YYYY'' ';
l_body:='<TABLE BORDER=1 BGCOLOR="#EEEEEE">';
l_body:=l_body||'<TR BGCOLOR="BLACK">';
l_body:=l_body||'<TH><FONT COLOR="WHITE">User</FONT>';
l_body:=l_body||'<TH><FONT COLOR="WHITE">First Logon</FONT>';
l_body:=l_body||'<TH><FONT COLOR="WHITE">Last Logon</FONT>';
l_body:=l_body||'<TH><FONT COLOR="WHITE">Machine</FONT>';
l_body:=l_body||'<TH><FONT COLOR="WHITE">No. Sessions</FONT>';
l_body:=l_body||'</TR>';

FOR c IN (
SELECT
Osuser,
Machine,
MIN(Logon_Time) First_Logon,
MAX(Logon_Time) Last_Logon,
Count(*) Total_Sessions
FROM
V$SESSION
GROUP BY
Osuser,
Machine
ORDER BY
5 Desc, 1 Asc )
LOOP
l_body:=l_body||'<TR>';
l_body:=l_body||'<TD>'||c.Osuser||'</TD>';
l_body:=l_body||'<TD>'||c.First_Logon||'</TD>';
l_body:=l_body||'<TD>'||c.Last_Logon||'</TD>';
l_body:=l_body||'<TD>'||c.Machine||'</TD>';
l_body:=l_body||'<TD>'||c.Total_Sessions||'</TD>';
l_body:=l_body||'</TR>';
END LOOP;
l_body:=l_body||'</TABLE>';

f:=fileType.open( 'C:\Temp', 'test.html');
f.Put_Line( l_body );
f.close;

html_email(
p_to => 'someone@somewhere',
p_from => 'someoneelse@somewhereelse',
p_subject => 'Usage Report',
p_text => 'A bit of text',
p_html => l_body,
p_smtp_hostname => '9.9.9.9',
p_smtp_portnum => '25');
end;
/



Tom Kyte
May 06, 2002 - 7:25 am UTC

There is no such builtin function -- "printable" is in the eye of the beholder I guess.

I would make a plsql package that contains a global that has what your concept of unprintable is, eg:

create package xxxx
as
g_unprintable varchar2(2000) default chr(0) || chr(1) || chr(2) || ....;
g_spaces varchar2(2000) default rpad( ' ', length(g_unprintable), ' ' );
end;


and then use:

translate( your_variable, g_unprintable, g_spaces );

to "fix" the string.

Solution for !

Vikas Sharma, September 15, 2002 - 11:55 am UTC

Hi,

I also faced the problem of ! as Mark Russell-Brown but later I found the solution for this. This is because the your variable l_body is getting concatenated in to a long string. What you should do just insert line break (chr(10)) at few place or may be where you again concatenate the l_body something like this :
l_body:=l_body||'<TD>'||c.Osuser||'</TD>'||chr(10);

This will solve your problem.

Thanks,

Vikas Sharma

HTML email

mo, April 24, 2003 - 2:28 pm UTC

Tom:

1. How would HTML look like in a Microsoft Outlook email program when the user opens it. Or would this be a link that the user opens up in a browser.

2. Is there any advantage of using HTML over ASCII for emails?

3. Would I be able to take a prcoedure that generates an HTML form and feed it into p_html parameter.

Thank you,

Tom Kyte
April 24, 2003 - 3:33 pm UTC

1) you'd have to ask someone who uses MS software products -- in mozilla, I can say they look like a web page.

2) some people consider them prettier. Me, I like 8 point Courier... and heavy use of the <pre> tag in my html

3) you can send an html form as an email, yes.

html email

mo, April 24, 2003 - 3:58 pm UTC

Tom:

Is not Mozilla an internet brower. You do not send email to a browser. DO you mean that some email programs can display HTML files just like a browser? Also, it seems the majority of people use MS outlook for desktop mail program.

Thank you

Tom Kyte
April 24, 2003 - 4:24 pm UTC

Mozilla is a web browser.
Mozilla is an email client.
Mozilla is a usenet newsgroup client.
Mozilla is an IRC/chat client.
Mozilla is an address book.
Mozilla is an html editor (composer).

That most people seem to use outlook isn't relevant. I don't, so I cannot answer as to what it would do. I find it painful to attempt to run MS software on my operating system of choice for my desktop and laptop.

html email

mo, April 26, 2003 - 2:48 pm UTC

Tom:

1. Can you please provide an example of sending the (select * from emp) in HTML format via the above procedure.

2. If I have a pl/sql procedure that creates HTML output can I set a variable of type long to the output of that procedure and then pass it here into the html email procedure?



Tom Kyte
April 26, 2003 - 3:09 pm UTC

1) do you see the input parameter p_html? all you need to do is put into a string the HTML you want to send. There is nothing magical here, you need to run the query, get the results, build a string with HTML in it and call this procedure.

2) tell me -- does your procedure return a long? if so, sure, if not -- if it is a "htp" type procedure, search this site for dump_page and use a technique similar to what I do there. I'm saving the output to a file, you would use your creativity to save the output "to a string".

html email

mo, April 28, 2003 - 11:59 am UTC

Tom:

1. I tested your html_email procedure for (select * from emp) and it worked fine. However as said before the p_text did not show up at all. Are you saying that the client will either pick the text or the html and I can not show both in he same message (MS outlook)?

2. When you do HTML emailing, do you assume that all users email clients can support HTML? If the client does not support HTML would the message still show up? Would you only implement this if you know that users can display HTML in email clients?

Thank you,

Tom Kyte
April 28, 2003 - 12:58 pm UTC

1) when you do this -- you send in TEXT the same thing you send in HTML. The email client will pick EITHER OR, but not both.

It either shows the HTML (if it is capable of doing so and the user has permitted it) or it shows the TEXT.


2) you don't have to -- if they do not, the email client will how them the TEXT version.


Me, I actually do not email html (as I said -- courier is the only font I use). I send links

(you should know that -- you've seen the emails I send from asktom, just text)s

1900 byte chunks ?

Paul Druker, May 14, 2003 - 12:28 pm UTC

I've seen this "magic" number - 1900 byte chunks - couple times on this site, and I'm wondering where this number comes from.

The RFC 821 SMTP (</code> http://www.ietf.org/rfc/rfc0821.txt <code> says "The maximum total length of a text line including the <CRLF> is 1000 characters (but not counting the leading dot duplicated for transparency)." The same phrase is used in UTL_SMTP package description in Supplied PL/SQL Packages guide.

Somewhere on the web I found that there is "a restriction of 2000 on the length on a single call to utl_smtp.write_data"

However, I was able to send data chunks of more than 2000 characters. So what's the maximum length of "data" parameter in utl_smtp.write_data that we should not exceed?





Randy, October 06, 2003 - 11:10 am UTC

Tom,
Is there a limitation on how much data I can load into the p_html variable? I am running into a problem when my query generates long emails (around 70 rows). The error I receive is:

ORA-21560: argument 2 is null, invalid, or out of range
ORA-06512: at "SYS.DBMS_LOB", line 775
ORA-06512: at "AP32ADMIN.HTML_EMAIL", line 63
ORA-06512: at line 35

Can you suggest a work around?

Thanks,
Randy

Tom Kyte
October 06, 2003 - 11:24 am UTC

32k is the largest PLSQL variable -- but if you exceeded that -- it would have blown up BEFORE you called this routine. thats not the cause.

put a little bit of debugging in there -- see what it is blowing up on. my line "63" is blank -- so, i'm not even really sure where it is failing in your code.

Randy, October 06, 2003 - 11:44 am UTC

Thanks for the quick response. I think it has something to do with how I define p_html.

p_html => l_acq||'<br>'||l_eng||'<br>'||l_pa,


Line 63 is:

dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);



Tom Kyte
October 06, 2003 - 1:33 pm UTC

what is the length of


l_acq||'<br>'||l_eng||'<br>'||l_pa

Sending HTML using UTL_SMTP

Ray DeBruyn Enterprise Information Systems, October 16, 2003 - 6:13 pm UTC

This example was outstanding. We send quite a few emails with Web Toolkit Applications. Some email clients double-spaced the content and some did not, making the appearance poor in some cases. HTML formatting would give a uniform appearance. We also had problems with links in the email where the link wrapped and the portion on the next line was no longer part of the link causing it to fail. Savvy users had no problem, but we would get calls from others and have to explain what to do over the phone. Now we can use anchors.

add cc function in html_email procedure

RICHARD, October 29, 2003 - 9:47 pm UTC

Hi Tom,

If I want to add cc: function in your html_email procedure, can I simply add the following line

l_temp := l_temp || 'Cc: ' || p_to || chr(13) || chr(10);

Or I have to make other changes.

Please advice.




Tom Kyte
October 30, 2003 - 7:04 am UTC

that'll just update the headers -- you would need to add calls to

utl_smtp.rcpt( l_connection, p_to );

to actually SEND them the email. Those headers are just for "nice display on the email client", they don't control who actually gets the mail

Pl explain your comment "-- 1900 byte chunks"

Vikas Sharma, November 11, 2003 - 8:31 pm UTC

Hi Tom,

Can you please explain your comment
-- Send the email in 1900 byte chunks to UTL_SMTP
Is this the max limit in a one call of utl_smtp.write_data.

But i was able to send more data w/o using the 1900 chunks.(while LOOP).
if i send the data in the 1900 chunk it slows the execution.

Regards,
Vikas Sharma

Tom Kyte
November 12, 2003 - 7:10 am UTC

you can pick any size upto 32k you want to pick

Randy, December 01, 2003 - 8:53 am UTC

Tom,
I've been having some problems sending HTML using this UTL_SMTP procedure. I think the problem is with the SMTP server, but I can not confirm. I am able send this procedure to one address successfully. However, when I send the same procedure using 22 addresses from a table with a cursor loop the message never gets delivered. Any ideas on why this is happening? Is this a UTL_SMTP issue?

Thanks in advance!

Tom Kyte
December 01, 2003 - 5:41 pm UTC

any errors?

what does the mail queue on the server look like? how's about the "from account", it should get any bounces.

HTML not shown in outlook

Vikas Sharma, December 27, 2003 - 9:45 am UTC

Hi Tom,
Your routine works fine with the yahoo, hotmail client.
Is this is depend upon the domain also. Because when i send an HTML email to my a/c vikas@m-tech.demon.co.uk and retrives it in the outlook express i get the following.

begin

html_email(
p_to =>'vikas@m-tech.demon.co.uk',
p_from =>'vikas@m-tech.demon.co.uk',
p_subject =>'hemmm',
p_text => ' hemmlllllllllll',
p_html => ' Vikas Sharma',
p_smtp_hostname => 'mtlweb',
p_smtp_portnum =>25
);
end;

i get the following in my MS outlook.


--a1b2c3d4e3f2g1
content-type:text/html;

Vikas Sharma
--a1b2c3d4e3f2g1--


But when i send email using same routine to my hotmail a/c and retrive it in MS outlook then it works fine.

Could you pl tell me what is wrong here.

Thanks and Regards,

Vikas Sharma

Tom Kyte
October 22, 2019 - 2:44 am UTC

sorry, i cannot really debug outlook. we are just pumping out documented, industry standard MIME encoded email. They are not dealing with it properly.

I don't run any windows stuff so I cannot even take a look at it. the standards are all out there on the web, you'll have to play around and see what nuances outlook is imposing on you (i do know that lots of people are using this with outlook -- even inside oracle here where we use this procedure heavily -- and other emails clients without issue. could be an old version of outlook or something).

It worked

Vikas Sharma, December 28, 2003 - 4:00 am UTC

Hi Tom,

Thanks for your review.

I added the utl_tcp.CRLF after the following line in your routine.

l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10)||utl_tcp.CRLF;

and it worked. I think after writing the header if we insert a blank line ie utl_tcp.CRLF then outlook understands it.

Thanks and Regards,

Vikas Sharma


Tom Kyte
December 28, 2003 - 11:14 am UTC

thanks for the followup.

it should NOT require an extra blank line (we already have the crlf there -- chr(13) || chr(10) is a CR/LF pair.

but as least we know what tweak is required for outlook to be able to function.

How to change some part of E-mail message to bold

Arvind, December 29, 2003 - 11:14 pm UTC

Dear Tom,

I am using utl_smtp for generating mails from Oracle. Can you tell me that how to change some portion of message to bold/underline.

Thanks

Tom Kyte
December 30, 2003 - 9:46 am UTC

you'd use HTML.

like above.

work immediately after compilation

August Groh, February 05, 2004 - 4:24 am UTC

I start my search for an example of using HTML in the utl_smtp package at METALINK. No success

Remembering "ask Tom" I found this article.

After 15 minutes I was able to send mails in HTML format.

Many thanks for this article





Richard, February 12, 2004 - 11:31 am UTC

Hi Tom,

How are you. Thank you very much for your previous help. This is urgent. I got the error numeric or value error, I think the problem is that html content (using plsql variable) exceeds 32k in production, please advise what is the workaround in order to send html content email (greater than 32k). I am using this html send functionality.

Thank you very much for help.

Richard

Tom Kyte
February 12, 2004 - 12:36 pm UTC

you'll have to change p_text from a simple varchar2 type into a plsql table type and send an "array" of text to transmit (changing the send mail routine to iterate over the array of strings and utl-smtp each one) -- or pass a CLOB instead of a varchar2 and dbms_lob.substr through it all and have utl-smtp send each chunk.

The follow-up of over 32k html content.

Richard, February 12, 2004 - 3:28 pm UTC

Hi Tom,
Thank you very much for your quick response. This is the follow-up.
We only concern about html content over 32k (ignore text). Please refer to the original html_email codes on the site.

I am thinking

1. l_temp varchar2(32767); -- No need to change.
2. Instead of doing
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
We should change to
dbms_lob.copy(l_body_html, p_lob_html,dbms_lob.getlength(p_lob_html), l_offset, 1);

3. dbms_lob.substr() had been done by the original program, because it sends 1900 chunk by chunk.

I try to confirm that Item 2 is the only thing I need to do to send html content over 32k.

Please confirm and advise.

Thank you very much for help.

Richard





Tom Kyte
February 12, 2004 - 7:35 pm UTC

that is not the only change - you need to change p_html to a clob everywhere and change whatever else might need changing (sort of a simple change in all probability). that looks "right" but I haven't tested it (me, i would be sending an email with a LINK to a big page cause I despise getting big emails personally! 32k exceeds my threshold for pain.)

Richard, February 13, 2004 - 4:55 pm UTC

Hi Tom,

Thank you very much for help. The issue gets resolved (I have to change multiple places outside html_email to push string here and there into a clob and then pass into html_email.

Thank you very much and have great weekend.

Richard



HTML Email

Ruben, February 13, 2004 - 7:19 pm UTC

Tom, what is the meaning of value 'a1b2c3d4e3f2g1' for
the variable l_boundary varchar2(255)?
Because when I set to other value such as '5555555555', the procedure funcionality works fine. Why?

Thanks a lot.




Tom Kyte
February 14, 2004 - 4:58 pm UTC

it is some unique string that will not appear in the email itself. It is a "boundary", a demarcation string, something to look for to say "at the end"

Swedish characters

rd, February 18, 2004 - 8:59 am UTC

Hi TOM,

I use your HTML_MAIL code.
I replaced "charset=us-ascii" with charset=iso-8859-1'.
Still I fail to get swedish characters in the mail.

Pls advise.

TIA,
Rd

Tom Kyte
February 18, 2004 - 8:50 pm UTC

utl_smtp sends 7bit ascii by default

you'll have to use the "raw" interface. check out the supplied packages guide (available on otn if you don't have one).

clob to raw !!

rd, February 19, 2004 - 7:48 am UTC

TOM,

Thanks for the response.
Now my problem( could be dumb one) is can i use the same code with clob converted into raw(pls suggest if there is anyway at all) and then use UTL_SMTP.WRITE_RAW_DATA?

I am pretty new to RAW/CLOB data.

TIA,
Rd

Tom Kyte
February 19, 2004 - 11:19 am UTC

utl_raw.cast_to_raw( dbms_lob.substr( clob data ) );

sure.

Multiple Mail recepients in the HTML mail

A reader, July 10, 2004 - 5:18 am UTC

Hi TOM,

This is cool... Im able to send HTML using the above code, but im unable to figure out how to send this HTML mail to multiple recipients.

Thanks

Tom Kyte
July 10, 2004 - 9:21 am UTC

utl_smtp.rcpt( l_connection, p_to );

call that multiple times with multiple inputs and away you go.


for example, maybe:

l_to := p_to || ',';
loop
exit when l_to is null;
n := instr( l_to, ',' );
l_tmp := substr( l_to, 1, n-1 );
l_to := substr( l_to, n+1 );
utl_smtp.rcpt( l_tmp );
end loop;



Unable to send HTML to multiple recipients

A reader, July 12, 2004 - 5:35 am UTC

Hi TOM,

Thanks for the reply. I tried the method that you have specified in </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:255615160805 <code>link "Sending e-mail! -- Oracle 8i specific response".

I was trying to combine both to send multiple mails as

CREATE OR REPLACE PACKAGE mail_pkg1 AS
TYPE ARRAY IS TABLE OF VARCHAR2(255);
PROCEDURE send(p_sender_email IN VARCHAR2
,p_from IN VARCHAR2 DEFAULT NULL
,p_to IN ARRAY DEFAULT ARRAY()
,p_cc IN ARRAY DEFAULT ARRAY()
,p_bcc IN ARRAY DEFAULT ARRAY()
,p_subject IN VARCHAR2 DEFAULT NULL
,p_body IN VARCHAR2 DEFAULT NULL
,p_html IN VARCHAR2 DEFAULT NULL);
END;

CREATE OR REPLACE PACKAGE BODY mail_pkg1 AS
g_crlf CHAR(2) DEFAULT chr(13) || chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost VARCHAR2(255) := 'mailserver';

FUNCTION address_email(p_string IN VARCHAR2, p_recipients IN ARRAY)
RETURN VARCHAR2 IS
l_recipients LONG;
BEGIN
FOR i IN 1 .. p_recipients.COUNT
LOOP
utl_smtp.rcpt(g_mail_conn, p_recipients(i));
IF (l_recipients IS NULL)
THEN
l_recipients := p_string || p_recipients(i);
ELSE
l_recipients := l_recipients || ', ' || p_recipients(i);
END IF;
END LOOP;
RETURN l_recipients;
END;

PROCEDURE send(p_sender_email IN VARCHAR2
,p_from IN VARCHAR2 DEFAULT NULL
,p_to IN ARRAY DEFAULT ARRAY()
,p_cc IN ARRAY DEFAULT ARRAY()
,p_bcc IN ARRAY DEFAULT ARRAY()
,p_subject IN VARCHAR2 DEFAULT NULL
,p_body IN VARCHAR2 DEFAULT NULL
,p_html IN VARCHAR2 DEFAULT NULL) IS
l_to_list LONG;
l_cc_list LONG;
l_bcc_list LONG;
l_boundary VARCHAR2(255) DEFAULT 'a1b2c3d4e3f2g1';
l_body_html CLOB := empty_clob; --This LOB will be the email message
l_offset NUMBER;
l_ammount NUMBER;
l_temp VARCHAR2(32767) DEFAULT NULL;
l_date VARCHAR2(255) DEFAULT to_char(SYSDATE,
'dd Mon yy hh24:mi:ss');

PROCEDURE writedata(p_text IN VARCHAR2) AS
BEGIN
IF (p_text IS NOT NULL)
THEN
utl_smtp.write_data(g_mail_conn, p_text || g_crlf);
END IF;
END;

BEGIN
g_mail_conn := utl_smtp.open_connection(g_mailhost, 25);

utl_smtp.helo(g_mail_conn, g_mailhost);
utl_smtp.mail(g_mail_conn, p_from);

l_to_list := address_email('To: ', p_to);
l_cc_list := address_email('Cc: ', p_cc);
l_bcc_list := address_email('Bcc: ', p_bcc);

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) || chr(10);

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(l_body_html, FALSE, 10);
dbms_lob.WRITE(l_body_html, length(l_temp), 1, l_temp);

----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(p_body), l_offset, p_body);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' || chr(13) || chr(10) ||
chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(p_html), l_offset, p_html);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.WRITE(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;

utl_smtp.open_data(g_mail_conn);

writedata('Date: ' || l_date);
writedata('From: ' || nvl(p_from, p_sender_email));
writedata('Subject: ' || nvl(p_subject, '(no subject)'));

writedata(l_to_list);
writedata(l_cc_list);
writedata(l_bcc_list);

utl_smtp.write_data(g_mail_conn, '' || g_crlf);
WHILE l_offset < dbms_lob.getlength(l_body_html)
LOOP
utl_smtp.write_data(g_mail_conn,
dbms_lob.substr(l_body_html, l_ammount, l_offset));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
END LOOP;
utl_smtp.close_data(g_mail_conn);
utl_smtp.quit(g_mail_conn);
END;
END;

scott@ORCL.WORLD> begin
2 mail_pkg1.send
3 ( p_sender_email => 'someone@somewhere.com',
4 p_from => 'Alert Message <someone@anywhere.com>',
5 p_to => mail_pkg1.array( 'someone@somewhere.com',
6 'noone@somewhere.com' ),
7 p_cc => mail_pkg1.array( 'someone@somewhere.com' ),
8 p_bcc => mail_pkg1.array( 'someone@somewhere.com' ),
9 p_subject => 'This is a subject',
10 p_html => 'TEST' );
11 end;
12 /
begin
*
ERROR at line 1:
ORA-29279: SMTP permanent error: 501 Syntax error, parameters in command "MAIL FROM:Alert Message
<someone@anywhere.com>" unrecognized or missing
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 221
ORA-06512: at "SCOTT.MAIL_PKG1", line 54
ORA-06512: at line 2

Then when i try

scott@ORCL.WORLD>
scott@ORCL.WORLD> begin
2 mail_pkg1.send
3 ( p_sender_email => 'someone@somewhere.com',
4 p_from => 'someone@anywhere.com',
5 p_to => mail_pkg1.array( 'someone@somewhere.com',
6 'noone@somewhere.com' ),
7 p_cc => mail_pkg1.array( 'someone@somewhere.com' ),
8 p_bcc => mail_pkg1.array( 'someone@somewhere.com' ),
9 p_subject => 'This is a subject',
10 p_html => 'TEST' );
11* end;
scott@ORCL.WORLD> /
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 775
ORA-06512: at "SCOTT.MAIL_PKG1", line 80
ORA-06512: at line 2

What am i doing wrong here ?

Tom Kyte
July 12, 2004 - 11:22 am UTC

the second one is because you are to send both a TEXT and HTML input (if you don't want to, fix the code).

the first is because your email server is rejecting your from -- nothing to do with your "to"

Multiple recipients contd...

A reader, July 13, 2004 - 3:25 am UTC

Hi TOM,

Regarding the first error, it should not have come, as im currently using both html_email and mail_pkg package seperatly to send mails and they both work great seperatly.

But in combination they are throwing error and im not able to figure out why they work seperatly and together they would not, is there something to do with DBMS_LOB ?

Thanks and Regards

Tom Kyte
July 13, 2004 - 11:41 am UTC

I think i described the errors you are getting? use them separately and give them the same sorts of inputs and you'll get the same exact errors (add some debugging yourself please -- you have the code!)

Its working now.

A reader, July 14, 2004 - 11:25 am UTC

Thanks TOM :)

Things are working perfectly now...

A reader, August 13, 2004 - 7:41 pm UTC

Tom,

html_email works dandy, can you also show how to send gif/jpg through smtp.

Thanks.

Tom Kyte
August 14, 2004 - 1:08 pm UTC

now you are talking attachments -- unless you send a link.

search for

sending email javamail

you'll want to use the java mail api for that in 9i and before.

DEMO_MAIL package and code examples

Robert Pang, August 20, 2004 - 3:15 pm UTC

Check out our UTL_SMTP code examples on OTN at </code> http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html <code>There are code examples that show how to send emails in a variety of ways (e.g. html email, text/binary attachments, and even inlined images in emails).

Thanks.

Randy

Randy, November 15, 2004 - 2:01 pm UTC

Tom,
I've been using this procedure for a while now and all of the sudden my pre tags are double spaced. Nothing has changed in my code so I'm at a loss on how to correct this. Have you ever experienced anything similar with this procedure?

Thanks!

Sending HTML

chencho, December 02, 2004 - 12:17 pm UTC

Hi, Tom

I have a question and I need your help. I "create" a html file according your especifications, OK, but if I insert a tag ... l_body:='<TABLE BORDER=1 BGCOLOR="#EEEEEE">';
l_body:=l_body||'<IMG src="c:\logowin.gif">' I will not receive this image on my email. Can you help me?

Regards

Tom Kyte
December 02, 2004 - 12:31 pm UTC

you need to have it accessible on a webserver.

you are just sending a webpage, an html page. It can have URLs in it, the url is resolved on the client. unless that client has logowin.gif on c:\ -- it'll never find it.

So, put logowin.gif on a webserver and use </code> http://thatserver/img/logwin.gif <code>

Friendly Sender

jd, December 07, 2004 - 1:05 pm UTC

How can i have it so that it shows a "friendly" name in the sender...so instead of xxx@xxx.com it should show
the text "sender". i tried sender <xxx@xxx.com>
i tried "sender" <xxx@xxx.com> and none work
thanks

Tom Kyte
December 07, 2004 - 8:35 pm UTC

utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || FANCY STRING HERE || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);

Sending HTML

Chencho, December 09, 2004 - 6:57 am UTC

Thank you for your kindly cooperation. It works fine now.

Bevc, January 18, 2005 - 8:47 am UTC

Hi Tom,

i use your html_email procedure and works fine for text or html.
Now i would like send a url with parameter in the message body, for example:

http://wtsc1/Travel/ADPZeit.aspx?&Person=7060&Firma=99&Reise=2005000022&Name=Test

If i execute this with SQL*Plus i get this:

SQL> exec html_email('r.bevc@vkd.test.com','oracle@vkd.test.com','Test',' Test','
http://wtsc1/Travel/ADPZeit.aspx?&Person=7060&Firma=99&Reise=2005000022&Name=Test','53.148.240.19','25'
;
Geben Sie einen Wert für person ein: 
Geben Sie einen Wert für firma ein: 
Geben Sie einen Wert für reise ein: 
Geben Sie einen Wert für name ein: 

PL/SQL-Prozedur wurde erfolgreich abgeschlossen.

SQL> 

I get prompt for variables. 
How can i pass this url to message without prompt ?

Thanks !
 

Tom Kyte
January 18, 2005 - 10:04 am UTC

SQL> set define off 

Random Exclamations

Tim, February 08, 2005 - 7:13 pm UTC

I just wanted to provide some confirmation with regard to the random exclamations. I experienced the same problem and noticed that the exclamation kept coming it at around the same position regardless of what text was being concatenated. I tested Vikas Sharma's solution and it worked.

If you experience this problem w/ the HTML view of the email simply concatenate in a CHR(10) as your message gets lengthy and that should resolve the problem.

Thanks for the info.

Any difference with the functionality of this function across versions of 8i and 9i ?

Rachit Khanna, April 21, 2005 - 6:46 am UTC

Hi ,
I am facing an issue wherby when I test this function in two versions of Oracle vi. 8.1.7.0 and 9.2.0.4.
I observer the following.
1. In 8i the procedure seems to be working smoothly as long as the length of the data to be sent is limited .
2. However as soon as this data exceedas beyond some value,I see that this function fails.
3. This same email can succesfully go out through 9i.

The procedure and declaration of the data to be sent in both cases is long.
When I tried to change this to clob,I see that the procedure is failing at the UTL_SMTP step.
Any help in this regards would be really appreciated.

Thanks
Rachit

Tom Kyte
April 21, 2005 - 6:52 am UTC


2) "some value", and what would that be? "function fails", how?

Any difference with the functionality of this function across versions of 8i and 9i ?

Rachit Khanna, April 21, 2005 - 7:22 am UTC

Hi Tom,
Thanks for the prompt response.
I have checked the length it is 1800 characters .
The error message I get is ORA-06502 : PL/SQL Numeric or value error.



Tom Kyte
April 21, 2005 - 11:52 am UTC

example and error stack -- WHERE is it happening. multi-byte data?

aparna, May 16, 2005 - 8:13 am UTC

DBMS_LOB.WRITE( io_viol_text_array(I),2,1,TempHold1);
its gives the error that"No data found"

Tom Kyte
May 16, 2005 - 12:51 pm UTC

that would mean that

io_viol_text_array(i)

has no value then. You are subscripting an array element you never set.

hi

chinedu, December 04, 2005 - 3:01 pm UTC

hi pal what zup

Tom Kyte
December 04, 2005 - 3:21 pm UTC

I think you mean "Whassup"

</code> http://whassup.com/ <code>

whassup

A Reader, December 06, 2005 - 4:44 am UTC

Its really funny. This is called presence of mind and sense of humor. So Tom you provided info on "Whassup".

File with attachment more than 32 K is giving error

bipin, August 14, 2006 - 1:11 pm UTC


attach_base64 (conn => v_conn,
DATA => v_xml --Clob
mime_type => 'application/xml',
inline => True,
filename => 'attach.xml',
LAST => TRUE
);

I have downloaded the code from link available in the same . When tried to send a mail with attachment file less than 32K it is working fine. But for more than 32K it is giving error.

Please help me thanx in advance.

Tom Kyte
August 14, 2006 - 1:24 pm UTC

whose code is failing, what/which link?

I think I recognize this code

John Flack, August 14, 2006 - 2:26 pm UTC

The code is from the sample e-mail package from OTN. The problem is simply that this routine in the package uses a PL/SQL CLOB to contain the e-mail attachment. Reader - In PL/SQL a CLOB variable is the same thing as a VARCHAR2 - limited to 32K in size. You won't be able to send more in a single call. Instead, you will have to write the attachment in pieces. Start with a call to begin_attachment, then a loop with write_text to write the attachment contents, and end with a call to end_attachment.

Tom Kyte
August 14, 2006 - 3:04 pm UTC

thanks - I don't monitor all bits of code out there in the ether :)

bipin, August 15, 2006 - 10:58 am UTC

attach_base64 (conn => v_conn,
DATA => v_xml --Clob
mime_type => 'application/xml',
inline => True,
filename => 'attach.xml',
LAST => TRUE );

I have downloaded the code from link available in the same . When tried to send a mail with attachment file less than 32K it is working fine. But for more than 32K it is giving error.

It fails while attaching file greater than 32K. It is giving error while creating and attaching a file. I feel it must be from demo_mail package, which is not able to handle. Might be 32K limit of UTL_SMTP package.


What is text boundary

Ram, September 11, 2006 - 5:06 pm UTC

This is a follow-up of the answer for the main question. What is text boundary / HTML boundary? Wht do we need them for the HTML mail? Sorry for the very basix question. I'm trying th implement this into my package.
Thanks
Ram

Tom Kyte
September 11, 2006 - 6:15 pm UTC

a unique string that is used to delineate boundaries (begin/end of the stream of data)

see the original answer - we use "boundary" in there in an illustrative way I believe.

great script

gregsheu, November 09, 2006 - 7:30 pm UTC

Hi Tom:
This is an awesome script that helps and saves my time a lot, and I hope you don't mind if I add some comment and feedback.
I've encountered some problems in outlook as well, and I've figured out why. In my html, I have some loop to query the data from the database and tabulate them in html <table> tag. Somehow, the outlook always cuts dowm my html email and leaves the boundary line at the bottom like this
my html body
--a1b2c3d4e5f4g3h2i1--
something missing from here.

Initially, I thought, it was my code or the html body exceeds the 32767. After a long while of brutal trial and debug, I've found out it is because it needs one more carriage return in my loop. Here is my example.
for indx in my_data_cur loop
p_html := p_html || '<tr><td>' || indx.data_1 || '</td><td>' || indx.data_2 || '</td></tr>';
--this line solves my problem
p_html := p_html || chr(13) || chr(10);
end loop;
Before I add that carraige return line, the outlook always misses something from that part of loop, but it displays everything correctly after I add that line of CRLF. I know this is my case, but maybe this will help others as well.
This is my humble opinion.

Ashish, February 26, 2007 - 2:44 am UTC

Hi Tom
Is there any way to set the priority of an email sent through UTL_SMTP.
Tom Kyte
February 26, 2007 - 2:39 pm UTC

that would just be a header tag, so sure. You research how priority is set, and you just need to "output it",


just like we set:
l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);


that stuff - "priority", which is purely a GUI concept (doesn't affect how the mail is sent or anything) would be some tag there.

Ashish Rai, February 27, 2007 - 4:06 am UTC

Thanks a lot, my problem is solved now.

Missing LF at end of MIME footer/boundary?

Wayne, May 04, 2007 - 4:16 pm UTC

Why is there no ending line feed (chr(10)) on the following line of code?

-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);

Awesome utility

bc, October 15, 2007 - 12:19 pm UTC

Tom,

I have been using this great utility for a while now and it works great all the time.

I was wondering is there a way to check for email bounces thru pl/sql or java code ?
Tom Kyte
October 15, 2007 - 3:00 pm UTC

well, bounced email goes back to the email address you used to "send" this.

so, you could

a) set up a special email account
b) have the mail bounce there
c) using the java mail api, read that inbox using POP or IMAP

so, yes, you could.

Thanks

bc, October 16, 2007 - 12:11 am UTC

Thanks Tom, I have done step 1 and 2, now would you happen to have an example or point me to some resources that can help me do step 3.

BC

PLZ HELP WHAT SHOUD I DO

SHAHZAD, December 30, 2007 - 6:43 am UTC

I M SHAHZAD.
I HAVE SOME PROBLEM.
I M NEW IN ORACLE PROGRAMING I HAVE DONE SQL
AND PREPERING PL-SQL
PLZ HELP
I M CREATING A CALENDER IN THIS PROCESS
2006
JANUARY 1
JANUARY 2
JANUARY 3
.. 30
FEB 1
.. 30
..
DEC 30
2007
JANUARY 1
JANUARY 2
JANUARY 3
.. 30
FEB 1
.. 30
..
DEC 30

I WANT TO CREAT THAT BY LOOPING
PLZ HELP AND SEND ME PROGRAME PLZ
I M WAITING I HAVE NO TIME


Encoding Problem

Davut Uysal, January 17, 2008 - 9:18 am UTC

Thanks a lot for the sample. It works great. But I have one problem with the encoding. I cannot see Turkish characters in the mail. What should I do to do it?

Thanks.

Not 100%

Farrukh, April 04, 2008 - 5:37 am UTC

This procedure is very useful but when it sends email to

Hotmail and Yahoo accounts, Some part of the Html body is

bieng truncated from the middle part of the body to the end.

Why is this happening Plzzzzzzzz, Help me!
Tom Kyte
April 04, 2008 - 10:29 am UTC

and when it sends it NOT to hotmail or yahoo - at the same time - does it "work"

if it does send "normally", problem is NOT here, but elsewhere.


or you have badly formed HTML (that is my first hunch, that your html itself is 'bad' and when yahoo/hotmail - which use html themselves - display it - the page is hosed.

actually - view the page source and see if the entire email isn't there - just not being displayed due to malformed HTML

Clarrification

Venkateswaran AS, April 07, 2008 - 10:49 am UTC

What is the meanin of empty_clob in
cBodyHTML CLOB := empty_clob;

Just curious to know.
Tom Kyte
April 08, 2008 - 11:27 am UTC

it was not necessary.

Is it secure to send Email from Oracle Database

Muhammad Waseem Haroon (ocp_waseem), April 17, 2008 - 2:44 am UTC

Hi Tom,

Our requirement is to send email from the business application to the internet world without using any SMTP Server.

1 - is it possible to send it without SMTP Server?
2 - If it is possible, is it secure?

One of my colleague is disagreeing to send email from database. He is suggesting to send it from VB.Net. His point of view is, to secure our database from internet world, it should not be open to the world.

Please suggest in detail with comparison.

Thanks in Advance.

Muhammad Waseem Haroon
Emirates Group IT - Mercator
m.waseem@emirates.com
ocp_waseem@hotmail.com
Tom Kyte
April 17, 2008 - 7:45 am UTC

... Our requirement is to send email from the business application to the internet
world without using any SMTP Server.
...

well, good luck with that. You sort of need SOMETHING to transport the mail.


Your friend is not aware of firewalls and how to secure a network??@!?! How does your friend propose to have VB.net (a programming infrastructure) connect to this database - apparently your friend doesn't want it plugged into the network (for if vb.net can get to database, and your vb.net program can send mail, then obviously the database is on the network and is capable of having INBOUND traffic - and therefore OUTBOUND traffic....)

whatever.

You shouldn't post your email address like that.... I cannot stop anyone from spamming it or anything when you make it public.

thank u tom

DEV, May 01, 2008 - 6:40 pm UTC

thank u tom u r a genious n if ever get n opportunity id love to meet u to have some more guidance
Tom Kyte
May 01, 2008 - 9:52 pm UTC

That won't happen, you need a new keyboard apparently - before we could even think about communicating.

smtp error

ved, May 02, 2008 - 3:48 am UTC

hi tom ive used the code here n it got created well but on executing it i get this error

ERROR at line 1:
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 138
ORA-06512: at "SYS.HTML_EMAIL", line 17
ORA-06512: at line 1

tom to be specific im using this code to send mail through oracle 9i within my domain(intranet to 8 users its a small project im working on..my domain name is example.com internally so...check me where am i wrong

regards

VED
Tom Kyte
May 02, 2008 - 7:18 am UTC

... ORA-29278: SMTP transient error: 421 Service not available ...

did you see that, nothing to do with Oracle.

if you tried to send email from the command line, you'd get the same thing, your smtp server is not set up there.

re

ved, May 02, 2008 - 9:10 am UTC

but tom how do i go about it your response was not that clear to me.....i am using an free email server for win xp and i've created a domain example.com and mail.example.com but what im not clear of is if i dont execute........bit confused..it would be clear if u explain it to me
regards

ved
Tom Kyte
May 02, 2008 - 10:32 am UTC

"U" isn't available.

Tell YOU what - telnet to your smtp server

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805#21531229621293


and you'll see that your "free email server" is apparently "not accepting email at this time"


You sort of need a bit more infrastructure set up in your environment beyond just getting an smtp server - you need to have all of the other stuff setup that goes with it.

Suggestion: see your network administrator, tell them what you are doing, get their assistance.

mail

A reader, May 02, 2008 - 9:56 am UTC


Has Oracle incorporated this type of code...

Eric, June 06, 2008 - 2:55 pm UTC

in the supplied packages since this was originally written?
Tom Kyte
June 09, 2008 - 10:30 am UTC

what type of code?!?!?!?

Thanks for the question regarding "Sending HTML using UTL_SMTP"

Chris, June 12, 2008 - 10:52 am UTC

Hi Tom,

Your email code works great. Thanks!

Now i have to send to multiple addresses and integrated the code below into my own code.

--------------
utl_smtp.rcpt( l_connection, p_to );

call that multiple times with multiple inputs and away you go.


for example, maybe:

l_to := p_to || ',';
loop
exit when l_to is null;
n := instr( l_to, ',' );
l_tmp := substr( l_to, 1, n-1 );
l_to := substr( l_to, n+1 );
utl_smtp.rcpt( l_tmp );
end loop;

------------------

My code loops through and each time extracts the next address and sends a new email. My problem is that ONLY the first email arrives correctly. In it, the email displayed is either plain text or html depending on the client. Works like a charm.

Unfortunately the 2nd email and all others following that arrive in plain text format but the code that is displayed is both the plain text version as well as the html version.

This is an example of what all the email besides the first look like,...

--a1b2c3d4e3f2g1--
MIME-Version: 1.0
To:
From: Me@somewhere.com
Subject: Document Review Request
Reply-To: Me@somewhere.com
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii

Plain text email is here.

--a1b2c3d4e3f2g1
content-type: text/html;

The html version and all the html code is displayed here.

--a1b2c3d4e3f2g1--

-------------------

I'm hoping you can give me some tips on where to look in trouble-shooting this problem.

Tom Kyte
June 12, 2008 - 11:04 am UTC

...
My code loops through and each time extracts the next address and sends a new
email.
....

why not just send one email with multiple recipients?

without a full complete example, I don't really know what you did or why it is doing what it is doing..... (make it small, as small as you can)

Thanks for the question regarding "Sending HTML using UTL_SMTP"

Chris, June 12, 2008 - 12:10 pm UTC

Hi again,

Sent a question earlier today but have figured it out. I forgot to null the l_temp variable out at the beginning of each loop. I am now and it works perfectly.

Thanks for your oracle tips, best on the net!!

Auto Mail Sender

Nana, June 18, 2008 - 1:00 am UTC

Hi Tom,

I wonder my Oracle 8i can use UTL_SMTP package or not. UTL_SMTP package can be an auto mail sender or not, meaning that, I want to send email containing data from my database and send the email daily. So, I want to set a schedule when the email should be sent.

Please advise me on this.
Thanks in advance!
Tom Kyte
June 18, 2008 - 12:57 pm UTC

well, umm, the code above is from an 8i question so - sure.

dbms_job is used to schedule stored procedures. write a stored procedure that sends the mail you want and dbms_job to schedule it.

numbers problem

Mohammad Montaser, February 28, 2009 - 2:09 pm UTC

i need to send mail in arabic language so i change charset to windows-1256 and i use:

utl_smtp.write_raw_data(l_connection,utl_raw.cast_to_raw( dbms_lob.substr( clob data ) ));

it works fine
but when i use this procedure through oracle application express and e-mail body includes numbers it give me this error:

ORA-29279: SMTP permanent error: 554 Your email is considered spam (8.40 spam-hits)

if it contains characters only it works fine

Tom Kyte
March 03, 2009 - 10:00 am UTC

umm, this is coming from your smtp server, it considered (based on the rules you or your smtp administrator put in place) your email to be spam.

Not sure what we could do about that in the database?

Sending mail in HTML format

prashant, April 14, 2009 - 11:29 am UTC

Hi Tom,

When i tried the above given procedure it is giving the following pates error msg.
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 819
ORA-06512: at "SCOTT.HTML_EMAIL", line 55
ORA-06512: at line 1

Can u pls help me on this..???
Tom Kyte
April 14, 2009 - 12:26 pm UTC

"U" isn't available, "U" is dead as far as I know. Look it up, it is true.

http://en.wikipedia.org/wiki/U_of_Goryeo




you are a programmer/developer right? Look at line 55, debug what is happening there. before you just cut and paste any code from the internet, you would sort of "make it your own" right - you have to roll it into your production code, so - make sure it meets your coding standards, that you know what it does and how it does it, and.....

debug it. Everyone takes the code and tweaks it a bit, I've no clue what line 55 of your code is - but you sure do!
ops$tkyte%ORA10GR2> create or replace procedure html_email(
  2      p_to            in varchar2,
  3      p_from          in varchar2,
  4      p_subject       in varchar2,
  5      p_text          in varchar2 default null,
  6      p_html          in varchar2 default null,
  7      p_smtp_hostname in varchar2,
  8      p_smtp_portnum  in varchar2)
  9  is
 10      l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
 11      l_connection    utl_smtp.connection;
 12      l_body_html     clob := empty_clob;  --This LOB will be the email message
 13      l_offset        number;
 14      l_ammount       number;
 15      l_temp          varchar2(32767) default null;
 16  begin
 17      l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum
 18  );
 19      utl_smtp.helo( l_connection, p_smtp_hostname );
 20      utl_smtp.mail( l_connection, p_from );
 21      utl_smtp.rcpt( l_connection, p_to );
 22
 23      l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
 24      l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
 25      l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
 26      l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
 27      l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
 28      l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
 29                           chr(34) || l_boundary ||  chr(34) || chr(13) ||
 30                           chr(10);
 31
 32      ----------------------------------------------------
 33      -- Write the headers
 34      dbms_lob.createtemporary( l_body_html, false, 10 );
 35      dbms_lob.write(l_body_html,length(l_temp),1,l_temp);
 36
 37
 38      ----------------------------------------------------
 39      -- Write the text boundary
 40      l_offset := dbms_lob.getlength(l_body_html) + 1;
 41      l_temp   := '--' || l_boundary || chr(13)||chr(10);
 42      l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
 43                    chr(13) || chr(10) || chr(13) || chr(10);
 44      dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
 45
 46      ----------------------------------------------------
 47      -- Write the plain text portion of the email
 48      l_offset := dbms_lob.getlength(l_body_html) + 1;
 49      dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);
 50
 51      ----------------------------------------------------
 52      -- Write the HTML boundary
 53      l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
 54                      chr(13) || chr(10);
 55      l_temp   := l_temp || 'content-type: text/html;' ||
 56                     chr(13) || chr(10) || chr(13) || chr(10);
 57      l_offset := dbms_lob.getlength(l_body_html) + 1;
 58      dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
 59
 60      ----------------------------------------------------
 61      -- Write the HTML portion of the message
 62      l_offset := dbms_lob.getlength(l_body_html) + 1;
 63      dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
 64
 65      ----------------------------------------------------
 66      -- Write the final html boundary
 67      l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
 68      l_offset := dbms_lob.getlength(l_body_html) + 1;
 69      dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
 70
 71
 72      ----------------------------------------------------
 73      -- Send the email in 1900 byte chunks to UTL_SMTP
 74      l_offset  := 1;
 75      l_ammount := 1900;
 76      utl_smtp.open_data(l_connection);
 77      while l_offset < dbms_lob.getlength(l_body_html) loop
 78          utl_smtp.write_data(l_connection,
 79                              dbms_lob.substr(l_body_html,l_ammount,l_offset));
 80          l_offset  := l_offset + l_ammount ;
 81          l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
 82      end loop;
 83      utl_smtp.close_data(l_connection);
 84      utl_smtp.quit( l_connection );
 85      dbms_lob.freetemporary(l_body_html);
 86  end;
 87  /

Procedure created.

ops$tkyte%ORA10GR2> exec html_email( 'tkyte@localhost', 'tkyte@localhost', 'testing', 'Hey there!', '<html>hi!!</html>', 'localhost', 25 );

PL/SQL procedure successfully completed.


prashant, April 14, 2009 - 1:16 pm UTC

Hi Tom,

Thank's now its working..... Earlier i didn't provided that html parameters.....

stephen, April 28, 2009 - 11:02 pm UTC

i have called the progrm like below,

declare

i integer;
raw_1 raw(2000);
conn utl_smtp.connection;
message varchar2(200);
begin

message := 'Testing,mail,attachement';
raw_1 := utl_raw.cast_to_raw(message);
conn := mail_attach.begin_mail('stephen@singpromo.com',
'stephen@singpromo.com',
'test_attachemnt',
'multipart/mixed');
mail_attach.attach_base64(conn,
raw_1,
'multipart/mixed',
TRUE,
'test.txt',
FALSE);
mail_attach.end_mail(conn);

end;


--the email message looks like this.. No attachemnts----

This is a multi-part message in MIME format.
-------7D81B75CCC90D2974F7A1CBD
Content-Type: multipart/mixed
Content-Disposition: inline; filename="test.txt"
Content-Transfer-Encoding: base64

VGVzdGluZyxtYWlsLGF0dGFjaGVtZW50


---What would be the problem

Tom Kyte
April 29, 2009 - 8:54 am UTC

guess you need to debug your mail_attach package? I mean, it is your code after all??

mail_attach is unknown to us.

mail_attach is demo_mail

Stephen, April 30, 2009 - 2:43 am UTC

Hi mail_attach is the same demo_mail from
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/maildemo_sql.txt
Just changed the name.
Tom Kyte
April 30, 2009 - 10:02 am UTC

and how would anyone know that?

That is not my code
You'll have to debug it if you feel the demo is doing something wrong.


but the email looks correct to me?? I see the mime header, I see the base64 encoded text?

mail attachment

stephen, May 01, 2009 - 8:45 am UTC

Hi, I was wondering , why it is not coming as an attachment.
Tom Kyte
May 01, 2009 - 10:03 am UTC

This is a multi-part message in MIME format.
-------7D81B75CCC90D2974F7A1CBD
Content-Type: multipart/mixed
Content-Disposition: inline; filename="test.txt"
Content-Transfer-Encoding: base64

VGVzdGluZyxtYWlsLGF0dGFjaGVtZW50



it is?


you have no body, the content disposition is "inline", the email client is free to do whatever it likes with this one.

question

A reader, May 12, 2009 - 2:31 am UTC

can we use a sql statement in utl_smtp and send the result with it ?
Tom Kyte
May 13, 2009 - 10:14 am UTC

describe to us what is means to "use a sql statement in utl_smtp" please?

I don't understand what you mean by that.

Ahmad Zahabi, May 18, 2009 - 12:15 pm UTC

hi Tom,
when I exec this proc.,I faced with 'ORA-29278: SMTP transient error: 421 Service not available' error.could you help me?Plz!

Thanks
Ahmad
Tom Kyte
May 23, 2009 - 11:19 am UTC

fix the transient error? It is not Oracle saying this, it is your SMTP server


Tell you what - telnet to your smtp server

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:255615160805#21531229621293

and type in the commands, see what you see without Oracle - do this on the DATABASE SERVER

Sending e-mail

Hamed Al Najjar, July 29, 2009 - 6:08 am UTC

Hi tom
I exec procedure is run without any error but aim not receiving any mail
Please help me
Thank
Hamed Najjar
CREATE OR REPLACE PROCEDURE mail
(
sender IN VARCHAR2,
recipient IN VARCHAR2,
ccrecipient IN VARCHAR2,
subject IN VARCHAR2,
message IN VARCHAR2
) IS

crlf VARCHAR2(2):= UTL_TCP.CRLF;
connection utl_smtp.connection;
mailhost VARCHAR2(30) := 'momraex01.momra.net';
header VARCHAR2(1000);

BEGIN

-- Start the connection.

connection := utl_smtp.open_connection(mailhost,25);

header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf||
'From: '||sender||''||crlf||
'Subject: '||subject||crlf||
'To: '||recipient||crlf||
'CC: '||ccrecipient;

-- Handshake with the SMTP server

utl_smtp.helo(connection, mailhost);
utl_smtp.mail(connection, sender);
utl_smtp.rcpt(connection, recipient);
utl_smtp.rcpt(connection, ccrecipient);
utl_smtp.open_data(connection);

-- Write the header

utl_smtp.write_data(connection, header);

utl_smtp.write_data(connection, crlf ||message);
utl_smtp.close_data(connection);
utl_smtp.quit(connection);

EXCEPTION
WHEN UTL_SMTP.INVALID_OPERATION THEN
dbms_output.put_line(' Invalid Operation in SMTP transaction.');
WHEN UTL_SMTP.TRANSIENT_ERROR THEN
dbms_output.put_line(' Temporary problems with sending email - try again later.');
WHEN UTL_SMTP.PERMANENT_ERROR THEN
dbms_output.put_line(' Errors in code for SMTP transaction.');
END;
-----
execute mail(' "Parag" ','hamed_alnajjar@hotmail.com','hamed_alnajjar@hotmail.com','Thanks Tom' , 'Tom is my last hope for any Oralce Problem...')
Tom Kyte
July 29, 2009 - 7:26 am UTC

maybe you have no serveroutput - remove the exception block as a first start.

then also start checking your mail queues. if you send email from that server - without using oracle - do you get it?

you know, debugging 101 - remove things - remove components - see what works and what does not work. remove oracle from the equation - start by proving "email from this machine works" and work out from there.

ORA-22275

A reader, September 02, 2009 - 1:37 pm UTC

Hi Tom,

Thank very much for providing the html_email proc. I've created the proc and it worked for a couple of weeks. Then our DB was refreshed using Prod instance. Now, each time I run the proc it gives me an ORA-22275 error.

How can I resolve this error?

Your help is greatly appreciated as always.

Bonnie
Tom Kyte
September 02, 2009 - 2:12 pm UTC

$ oerr ora 22275
22275, 00000, "invalid LOB locator specified"
// *Cause:  There are several causes:  (1) the LOB locator was never
//          initialized; (2) the locator is for a BFILE and the routine
//          expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
//          BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
//          (4) trying to update the LOB in a trigger body -- LOBs in
//          trigger bodies are read only; (5) the locator is for a
//          BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
//          (6) the locator is for a CLOB/NCLOB and the routine expects
//          a BFILE/BLOB locator;
// *Action: For (1), initialize the LOB locator by selecting into the locator
//          variable or by setting the LOB locator to empty.  For (2),(3),
//          (5) and (6)pass the correct type of locator into the routine.
//          For (4), remove the trigger body code that updates the LOB value.



time to debug the code - probably there is a null in there - that would be my guess.


but you have ALL of the code, you have the offending line number - you know where in the code it is blowing up, now - debug it and see what is the root cause with your data.

excellent

A reader, September 24, 2009 - 4:51 pm UTC

thanks is very useful.



Email HTML

Francis, October 10, 2009 - 6:16 pm UTC

After all these years, this is the best source I found with regards to HTML email. I can NOT find anything worthy in metalink (My Oracle Support is now being called).

Great job! Thank you very much!!!

UTL_SMTP

Rajkumar, October 14, 2009 - 7:53 am UTC

The blog which I read through was very much useful and it resolved all my email issues for whcih I was struggling and confusing for almost 2 whole days.
Thanks for the support.

Arabic content

A reader, December 21, 2009 - 3:09 am UTC

Hi,

My issue is regarding Arabic character.
I set the charset to windows-1256 and even tried with other different charset like UTF-8.

I used also: utl_smtp.write_raw_data(l_connection,utl_raw.cast_to_raw(
dbms_lob.substr(data).

Before i set all these changes to the procedure i was receiving the Arabic text as '??????'

However, after above modifications, the text display changed to something like this : ÇäÇÓààååÑêå

Does the problem still in the DB side or it can be coming from mail server ??

Thanks,
Mariam
Tom Kyte
December 21, 2009 - 2:54 pm UTC

SMTP uses 7bit ascii data. You would have to encode other data.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_smtp.htm#CHDCFFDI

Single byte data is all it can do, you'd have to send multibyte data as an attachment - base 64 encoded.


A reader, December 21, 2009 - 11:41 pm UTC

Thanks for your respond :)

unfortunately the purpose is to send the content as email text not as an attachment.

what is written in page u posted didn't mention that there is a problem with sending multibyte since the SMTP server supports 8BITMIME extension. I think my server is doing that since we can send Arabic text through it without any problem in displaying the character.

there is a guy in this page as well followed same steps i did and he didn't mention anything about attachment.

Is that mean there is no possibility to do that without attachment?

Thanks and sorry for any inconvenience.

Tom Kyte
December 23, 2009 - 7:14 am UTC

I don't know, I've never dealt with that language.

Sending email to multiple receipients

Preeti, January 13, 2010 - 10:48 am UTC

Thank you Tom for your example.
I followed it and working fine.I am curious to know ..how can I add multiple recipients to the email and in bcc

I work at a college and we cannot send email to students if it shows other student email address
preeti
Tom Kyte
January 18, 2010 - 4:26 pm UTC

yes, you can bcc that way.

basically, to bcc, you just leave out

l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);


don't send a to: record

Latin characters

Andrés, February 25, 2010 - 5:50 am UTC

Hi,

I adapted the original procedure for accept latin characters 'áéíóúüñ' and work fine!

I use utl_tcp.open_connection.


create or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
v_conexion utl_smtp.connection;
v_conexion_tcp utl_tcp.connection;
begin
v_conexion_tcp:=utl_tcp.open_connection(remote_host=>p_smtp_hostname,remote_port=>p_smtp_portnum,charset=>'AL32UTF8');
v_conexion.host := v_conexion_tcp.remote_host;
v_conexion.port := v_conexion_tcp.remote_port;
v_conexion.tx_timeout := v_conexion_tcp.tx_timeout;
v_conexion.private_state := NULL;
v_conexion.private_tcp_con := v_conexion_tcp;
-- l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( v_conexion, p_smtp_hostname );
utl_smtp.mail( v_conexion, p_from );
utl_smtp.rcpt( v_conexion, p_to );

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type:multipart/alternative;boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type:text/plain;charset=utf-8' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type:text/html;charset=utf-8' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(v_conexion);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(v_conexion,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(v_conexion);
-- utl_smtp.quit( l_connection );
utl_tcp.close_connection(v_conexion_tcp);
dbms_lob.freetemporary(l_body_html);
end;

A reader, May 04, 2010 - 12:23 pm UTC

Hello; tom

You write this html_mail bag I find it very good, I carefully read this code, but there is a place I still do not understand why a request to explain to you;
Why this piece of code where the message body of each interception clob 1900 characters then? The 1900 How did you come up with this, or that there are specific documents to support, please tell me, it confused me for a long time, thank you.

-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;

I tried more than 32k of html e-mail if the body assigned to clob, then the code above in the 1900 revised to 10,000, then the html mail <tr> <td> table deformation, with the 1900 will not, thank you

Please excuse my confusion, why choose it in 1900, thank you
Tom Kyte
May 06, 2010 - 1:30 pm UTC

I did not code the html_email procedure, I'll ask Tyler to comment :)

1900? Not sure

Tyler Muth, May 06, 2010 - 1:54 pm UTC

I wish I remembered why it was in 1900 byte chunks, but that was 9 years ago. I guess I should have been more specific with the comment.

Tyler

Thank you for your reply, sorry I did not find, thank you

future, May 08, 2010 - 10:17 am UTC

hi,Taylor

My apologies, 9 years ago, something has to give you advice, because that time, I not touch oracle.

I find that html_mail this process, find 1900bype in the interpretation of the results hard to find no

If you remember back, or where there this explanation, please tell me, or could give a new explanation, thank you
Tom Kyte
May 10, 2010 - 6:19 pm UTC

today, in the year 2010, any limit it was working around back then is not relevant anymore.

Feel free to increase it, test it and use it.

Or free free to leave it be, since it doesn't harm anything the way it is.

In short - it is your code now, all of the stuff on this site is a "demo to get the concept across" - feel free to use and reuse as you see fit.

Tried other suggestions but still no luck.

Daniel Carr, May 13, 2010 - 7:34 am UTC

Excellent piece of code and works fine for me except when I try to pass more than 8191 characters to the html_email procedure. I get the following error message:

ORA-06502: PL/SQL: numeric or value error

The code is breaking at the following line:

dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

length(p_html): 38025
l_offset: 375

I have tried changing the p_html from varchar2 to clob and have also changed the l_temp from varchar2(32767) to clob and am still getting an error.

I will do as you suggest and just send a reminder with a link but that's not what the client asked for. I also don't like abreviated messages, when I send SMS's it's in full. :o)

HTML EMAIL

Jagjit, May 17, 2010 - 11:11 am UTC

Use this to support CLOB data
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
-- dbms_lob.copy(l_body_html, p_html,dbms_lob.getlength(p_html), l_offset, 1);
----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
-- dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
dbms_lob.copy(l_body_html, p_html,dbms_lob.getlength(p_html), l_offset, 1);
----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);
-- dbms_lob.copy(l_body_html, p_html,dbms_lob.getlength(p_html), l_offset, 1);

Thanks for the help.

Daniel Carr, May 18, 2010 - 10:16 am UTC

Works perfectly! Thanks Jagjit, I've been working on this all last week and eventually gave up and just sent an email with a link back to my app.

UTL_SMTP

A reader, February 28, 2011 - 2:30 am UTC

Its working fine for me...CHR(10) a Line break after each code in HTML...

Thanks
Akshay

Issue related to UTL_SMTP

A reader, May 03, 2011 - 4:43 am UTC

I am sending mail using UTL_SMTP from PL SQL stroed procedure.The mail body is a HTML page having some messages along with a tabular list of user information.It is of datatype CLOB.While sending the mail message,I am reading in chunks of size 1900 bytes.
I have tried to check the 'charset' while sending the mail.I am using the following format:
UTL_SMTP.WRITE_DATA(c,'Content-Type: text/html;charset=UTF-8'|| CHR(13) || CHR(10));

Issue:
When I check the mail sent to my Lotus Notes ID, I could see some unecessary characters e.g. '!' in the body of the mail.There is also some issue with the data in the tabular format .When I check the same mail sent to my GMAIL ID,the mail looks fine without any issues in the mail body.
Tom Kyte
May 04, 2011 - 12:22 pm UTC

so why is this an issue with utl_smtp?

If you look at the raw email - ctl-u in thunderbird for example - does it look correct?

Adding CLOB functionality instead of varchar2?

Hayat, May 11, 2011 - 1:00 pm UTC

Hi Tom,
I went through this topic and few others as well for my Oracle 10g instance but unable to get the results. As my I need to pass more than 32k to p_html parameter. What I did is to create all html code in a CLOB variable and passing it to SMTP procedure provided above in this post by changing data type to CLOB in code like below but getting error while upto 32k it works fine.

create or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html CLOB, <<<<<<<<<<<<<<<<<<<<<
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)

I tried to follow your following comments but seems I am missing something.

Followup February 12, 2004 - 12pm Central time zone:

you'll have to change p_text from a simple varchar2 type into a plsql table type and send an
"array" of text to transmit (changing the send mail routine to iterate over the array of strings
and utl-smtp each one) -- or pass a CLOB instead of a varchar2 and dbms_lob.substr through it all
and have utl-smtp send each chunk.

Thanks in advance for your assistance.
Tom Kyte
May 11, 2011 - 1:50 pm UTC

for html_email, that doesn't apply. You are not USING p_text or even talking about p_text

You are concerned with p_html.

"works fine" implies when it exceeds 32k "it doesn't work fine", but saying "it doesn't work fine" is like me saying


my car won't start.


You'll need to be a little more precise in your description of things.

Are you a programmer? Have you tried debugging this a bit on your own, the routine is quite small.

I am using p_html

Hayat, May 12, 2011 - 4:41 am UTC

Hi Tom,
As I am passing to p_html the html formatted body as below,

email_html(p_to => '' || l_current_email || '',
p_from => my_email,
p_subject => l_email_subject,
p_text => 'NULL',
p_html => l_body,
p_smtp_hostname => l_smtp_hostname,
p_smtp_portnum => l_smtp_portnum);

so thats the reason I have not considered p_text. thanks
Tom Kyte
May 12, 2011 - 7:54 am UTC

I don't understand what you mean here.

YOU posted a bit of stuff I wrote regarding p_text, that was confusing at best since p_text isn't even involved.


And I sort of asked you above *what* the error is? What is it? We cannot diagnose anything without having precise, complete information.

are you a programmer? what debugging have YOU done so far - what have you discovered, what have you ruled out?

xxx, June 01, 2011 - 3:05 am UTC

hi tom,
is this html email supports for blob data.
i changed the procedurecreate or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in blob,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)

and i changed l_body_html blob:=empty_blob;

and html message procedure,i changed to l_body blob
it gives me the error "expression of wrong type of arguments"
how i can solve this.
my mail contains more than the capacity of clob data.

Hayat, June 02, 2011 - 12:30 am UTC

Hi All,
Here is how I managed it,

1- Updated in the original code as it is getting copied again and again,

l_ammount number; -- removed one m.

Hope it will be corrected here in this topic as well.

2- Changed the data type from varchar2 to clob of parameters.

p_html clob,

3- Following code changes in the code,

----------------------------------------------------
-- Write the HTML portion of the message
-- l_offset := dbms_lob.getlength(l_body_html) + 1;
-- dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);

l_offset2 := 1;
l_amount2 := 1900;

while l_offset2 < dbms_lob.getlength(p_html) loop
l_p_html:= dbms_lob.substr(p_html, l_amount2, l_offset2);

l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_p_html), l_offset, l_p_html);

l_offset2 := l_offset2 + l_amount2;
l_amount2 := least(1900, dbms_lob.getlength(p_html) - l_amount2);
end loop;

----------------------------------------------------
Now it is upto you to change the variables names as per your standards. I just added 2 just to differentiate.

xxx, June 02, 2011 - 3:37 am UTC

hi hayat,
according to jaggit reply i modified the procedure that supports to clob data.its working fine
now it supports up to some limit of data.my records from database is near around 122.upto 80 records its working fine.if it extends error was displayed.
how i can change the code.
i followed your reply also.
i modified according to you as mentioned changes but it gives as copy of the same .i need to display the whole records as html mail.

unknown, June 05, 2011 - 8:38 am UTC

hi tom,
the following code i applied to supports clob data.
sometimes it is working fine.
sometimes i gives the following error:
ORA-06502: PL/SQL: numeric or value error
how to solve this.
i observed that there is size problem.
how to modify the below code to overcome the error

create or replace procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in clob,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp clob default null;
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
utl_smtp.rcpt( l_connection, p_to );

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10)||utl_tcp.crlf;

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=Windows-1256' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html; charset=Windows-1256'' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
----dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
dbms_lob.copy(l_body_html, p_html,dbms_lob.getlength(p_html), l_offset, 1);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
end;
/

Tom Kyte
June 06, 2011 - 9:33 am UTC

well, it probably has to do with nulls at some point - but since

a) we don't know what inputs to use to reproduce the issue
b) we don't know where the issue is, we have no clue what line of code

we'll have to leave it up to you to figure out - use your debugging skills to be able to get (a), and then once you have that - you'll know the answer to (b) and you'll be able to inspect what values you are sending to some subroutine and the answer will be right there.

Hayat, June 13, 2011 - 9:50 am UTC

Please try the following,

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
----dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);
dbms_lob.copy(l_body_html, p_html,dbms_lob.getlength(p_html), l_offset, 1);

----------------------------------------------------

the above code need to be replace with the following,

----------------------------------------------------
-- Write the HTML portion of the message
-- l_offset := dbms_lob.getlength(l_body_html) + 1;
-- dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);

l_offset2 := 1;
l_amount2 := 1900;

while l_offset2 < dbms_lob.getlength(p_html) loop
l_p_html:= dbms_lob.substr(p_html, l_amount2, l_offset2);

l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_p_html), l_offset, l_p_html);

l_offset2 := l_offset2 + l_amount2;
l_amount2 := least(1900, dbms_lob.getlength(p_html) - l_amount2);
end loop;

----------------------------------------------------

HTML Email With Attachments

Saurabh, July 18, 2011 - 2:50 am UTC

Hi,

I want to send the email with HTML Contents and CSV attachments. Procedure shared by you works great for HTML content. I am also able to send the mails with attachments but not able to do both in a single mail.

Following is the modified code through which i am trying to send both in a single mail.

CREATE OR REPLACE procedure html_email(
p_to in varchar2,
p_from in varchar2,
p_cc in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
lv_message in varchar2 default null)
is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_body_html clob := empty_clob;
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
d_temp varchar2(32767) default null;
p_smtp_hostname varchar2(30):= '127.0.0.1';
p_smtp_portnum varchar2(30) := '25';
i number;
j number;
cc varchar2(100);
bcc varchar2(100);
crlf VARCHAR2(2) := chr(13)||chr(10);
begin
l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum );
utl_smtp.helo( l_connection, p_smtp_hostname );
utl_smtp.mail( l_connection, p_from );
begin
i := 1;
j := 1;
while instr(p_to,',',i) != 0 loop
cc := substr(p_to,i,instr(p_to,',',i)-j);
i := instr(p_to,',',i)+1;
j := i;
utl_smtp.rcpt( l_connection, cc );

end loop;
cc := substr(p_to,i,length(p_to));

utl_smtp.rcpt( l_connection, cc );
end;

begin
i := 1;
j := 1;
while instr(p_cc,',',i) != 0 loop
bcc := substr(p_cc,i,instr(p_cc,',',i)-j);
i := instr(p_cc,',',i)+1;
j := i;
utl_smtp.rcpt( l_connection, bcc );

end loop;
bcc := substr(p_cc,i,length(p_cc));

utl_smtp.rcpt( l_connection, bcc );
end;


l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'CC: ' || p_cc || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) ||
chr(10);

d_temp := d_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
d_temp := d_temp || 'To: ' || p_to || chr(13) || chr(10);
d_temp := d_temp || 'From: ' || p_from || chr(13) || chr(10);
d_temp := d_temp || 'CC: ' || p_cc || chr(13) || chr(10);
d_temp := d_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
d_temp := d_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
d_temp := d_temp || 'Content-Type: multipart/mixed;'|| utl_tcp.crlf || ' boundary="-----SECBOUND"'|| utl_tcp.crlf || utl_tcp.crlf ;


d_temp := d_temp || '-------SECBOUND'|| utl_tcp.crlf ||'content-type: text/plain;' || ' name="excel.csv"'|| utl_tcp.crlf || 'Content-Transfer_Encoding: 8bit'|| utl_tcp.crlf || 'Content-Disposition: attachment;'|| utl_tcp.crlf || ' filename="excel.csv"'|| utl_tcp.crlf || utl_tcp.crlf || lv_message || utl_tcp.crlf || utl_tcp.crlf || '-------SECBOUND--' ;

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary( l_body_html, false, 10 );
dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13)||chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=us-ascii' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/html;' ||
chr(13) || chr(10) || chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_SMTP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
utl_smtp.write_data(l_connection,d_temp );

while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_data(l_connection,
dbms_lob.substr(l_body_html,l_ammount,l_offset));
l_offset := l_offset + l_ammount ;
l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
end loop;


utl_smtp.close_data(l_connection);
utl_smtp.quit( l_connection );
dbms_lob.freetemporary(l_body_html);
end;

Special characteres on "to" parameter

neoon, November 14, 2011 - 6:36 am UTC

Can you please help me on this?

How can i change this code to accept "ã" or other special character?

I hope I'm phrasing this correctly...

When the email arrives it comes with an "?".

  procedure sendEmail(
    p_to            in varchar2,
    p_from          in varchar2,
    p_subject       in varchar2,
    p_text          in varchar2 default null,
    p_html          in varchar2 default null,
    p_cc in varchar2 default null,
    p_smtp_hostname in varchar2,
    p_smtp_portnum  in varchar2)
is
    l_boundary      varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection    utl_smtp.connection;
    l_body_html     clob := empty_clob;  --This LOB will be the email message
    l_offset        number;
    l_ammount       number;
    l_temp          varchar2(32767) default null;
begin
    l_connection := utl_smtp.open_connection( p_smtp_hostname, p_smtp_portnum);
    utl_smtp.helo( l_connection, p_smtp_hostname );
    utl_smtp.mail( l_connection, p_from );
    utl_smtp.rcpt( l_connection, p_to );
    utl_smtp.rcpt( l_connection, p_cc );

    l_temp := l_temp || 'MIME-Version: 1.0' ||  chr(13) || chr(10);
    l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
    l_temp := l_temp || 'CC: ' || p_cc || chr(13) || chr(10);
    l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
    l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
    l_temp := l_temp || 'Reply-To: ' || p_from ||  chr(13) || chr(10);
    l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||chr(34)||l_boundary||chr(34)||chr(13)||chr(10);
    
    ----------------------------------------------------
    -- Write the headers
    dbms_lob.createtemporary( l_body_html, false, 10 );
    dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


    ----------------------------------------------------
    -- Write the text boundary
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    l_temp   := '--' || l_boundary || chr(13)||chr(10);
    l_temp   := l_temp || 'content-type: text/plain; charset=us-ascii' ||
                  chr(13) || chr(10) || chr(13) || chr(10);
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the plain text portion of the email
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_text),l_offset,p_text);

    ----------------------------------------------------
    -- Write the HTML boundary
    l_temp   := chr(13)||chr(10)||chr(13)||chr(10)||'--' || l_boundary ||
                    chr(13) || chr(10);
    l_temp   := l_temp || 'content-type: text/html;' ||
                   chr(13) || chr(10) || chr(13) || chr(10);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

    ----------------------------------------------------
    -- Write the HTML portion of the message
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(p_html),l_offset,p_html);

    ----------------------------------------------------
    -- Write the final html boundary
    l_temp   := chr(13) || chr(10) || '--' ||  l_boundary || '--' || chr(13);
    l_offset := dbms_lob.getlength(l_body_html) + 1;
    dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);


    ----------------------------------------------------
    -- Send the email in 1900 byte chunks to UTL_SMTP
    l_offset  := 1;
    l_ammount := 1900;
    utl_smtp.open_data(l_connection);
    while l_offset < dbms_lob.getlength(l_body_html) loop
        utl_smtp.write_raw_data(l_connection,
                            utl_raw.cast_to_raw(dbms_lob.substr(l_body_html,l_ammount,l_offset)));
        l_offset  := l_offset + l_ammount ;
        l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
    end loop;
    utl_smtp.close_data(l_connection);
    utl_smtp.quit( l_connection );
    dbms_lob.freetemporary(l_body_html);
  end;


thanks

i've found it

neoon, November 14, 2011 - 7:32 am UTC

thanks for all

  procedure send (p_sender varchar2,
                          p_recipient varchar2,                    
                          p_cc varchar2,
                          p_subject varchar2,                  
                          p_body varchar2 default null) is    
    c utl_smtp.connection;
    v_recipient varchar2(1000);  
  begin    
    --make connection to smtp    
    c := utl_smtp.open_connection('mail.grupocgd.com', 25);    
    
    --identify the domain of the sender    
    utl_smtp.helo(c, 'mail.grupocgd.com');
        
    --start a mail, specify the sender    
    utl_smtp.mail(c, p_sender);
        
    --identify recipient    
    utl_smtp.rcpt(c, p_recipient);
    utl_smtp.rcpt(c, p_cc);
        
    --start the mail body    
    utl_smtp.open_data(c);
        
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw('From: ' || p_sender || utl_tcp.crlf));    
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw('To: ' || p_recipient || utl_tcp.crlf));
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw('CC: ' || p_cc || utl_tcp.crlf));    
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw('Subject: ' || p_subject || utl_tcp.crlf));  
    
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw('Content-Type: text/html' || utl_tcp.crlf));    
    utl_smtp.write_raw_data(c, utl_raw.cast_to_raw( 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; 

how do i change a review?

neoon, November 14, 2011 - 7:43 am UTC

how do i change a review?
how do i change a review?
how do i change a review?
how do i change a review?

Tom Kyte
November 15, 2011 - 8:37 am UTC

you do not
you do not
you do not
you do not


I do not make you log in
I do not want to make you log in

Unable to send the html email to iPhone

Vijay, November 29, 2011 - 11:05 am UTC

Hi Tom,

Great example. I have used your html_email example to send email to Gmail, Android, Outlook, Zimbra clients. However, i noticed when i sent the same email to a co-worker account on iPhone i received the following message

Could not deliver message to the following recipient(s):

Failed Recipient: <someone>@??.rr.com
Reason: Remote host said: 550 5.6.0 Invalid header found (see RFC2822 section 3.6)

How come the same email header was valid on MS, Mozilla and Gmail and various other clients but not a valid for Iphone ?

Any suggestion is appreciated.

Tks
Vijay



Following is the header information.
Subject: ?????????
Reply-To: senders Email
Content-Type: multipart/alternative; boundary="a1b2c3d4e3f2g1"
--a1b2c3d4e3f2g1
content-type: text/plain; charset=us-ascii


Tom Kyte
November 30, 2011 - 7:00 am UTC

get a dump of an email sent to the iphone and see what differs.

Should all be good as far as I know.

Output sent to mail id is displayed as a html code but not formatted output

Sreevidya, December 17, 2011 - 10:44 am UTC

Hi,

you have been helping us with very detailed information. i tested the html_email and i got the output in the following way to my email. However, I wanted to see a formatted output (e.g. formatted in the table form). Have I done some thing wrong?

Please let me know. It would be of great help to me.

thanks,

output
-------
--a1b2c3d4e3f2g1
content-type: text/html;

<TABLE BORDER=1 BGCOLOR="#EEEEEE"><TR BGCOLOR="BLACK"><TH><FONT
COLOR="WHITE">Evaluation Date</FONT><TH><FONT
COLOR="WHITE">CPU_Utilization</FONT><TH><FONT COLOR="WHITE">System
Idle</FONT><TH><FONT COLOR="WHITE">Database CPU
Ratio</FONT></TR><TR><TD>15-DEC-11 11.01.59.000000
PM</TD><TD>22.9800540906018</TD><TD>77.0199459093982</TD><TD>27.4916211330259</TD></TR></TABLE>
--a1b2c3d4e3f2g1--

Best Regards,
Sree
Tom Kyte
December 17, 2011 - 12:23 pm UTC

are you using the procedure unmodified?

are you viewing this in a 'normal' email client, which one?

Reply to my earlier post

Sreevidya, December 19, 2011 - 12:19 am UTC

Hi, I got what the source of the problem.

As you said rightly, its the mail provider settings and the email client that prohibits me to view the results properly.

Just Now, I have tested it with other provider and it is working.

This is an excellent post, I really appreciate the efforts placed by the authors!

Kudos!

Sree

Solving ! issue

Jason, January 02, 2012 - 9:41 pm UTC

Thank you Tyler Muth for the HTML e-mail procedure and thanks to Vikas Sharma for the post on resolving the "!" issue. It works perfectly!!!!!!

Attachement

Julio César, January 03, 2012 - 11:30 am UTC

Hi,

I've succesfully implemented de send_email procedure, but does anybody have any ideia of how i can implement an attachement to email body, just writting the data in the clob?

Query

VIKAS, January 09, 2012 - 8:23 am UTC

Oracle Package:-
CREATE OR REPLACE PACKAGE BODY TPCPROD.Mail_Package IS
PROCEDURE print_output(p_message IN VARCHAR2) IS
BEGIN
dbms_output.put_line(SUBSTR(p_message,1,500));

IF LENGTH(p_message) > 500 THEN
dbms_output.put_line(SUBSTR(p_message,500,1000));
END IF;

IF LENGTH(p_message) > 1000 THEN
dbms_output.put_line(SUBSTR(p_message,1000,1500));
END IF;

IF LENGTH(p_message) > 1500 THEN
dbms_output.put_line(SUBSTR(p_message,1500,2000));
END IF;
EXCEPTION
WHEN OTHERS
THEN
NULL; -- Ignore errors... protect buffer overflow's etc.
END print_output;
------------------------------------------------------------------------
FUNCTION dump_flatfile(
p_query IN VARCHAR2,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2,
p_separator IN VARCHAR2,
p_headers IN BOOLEAN DEFAULT FALSE,
p_trailing_separator IN BOOLEAN DEFAULT FALSE,
p_max_linesize IN NUMBER DEFAULT 32000,
p_mode IN VARCHAR2 DEFAULT 'w')
RETURN NUMBER
IS
l_output utl_file.file_type;
l_thecursor INTEGER DEFAULT dbms_sql.open_cursor;
l_columnvalue VARCHAR2(4000);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_cnt NUMBER DEFAULT 0;
l_separator VARCHAR2(10) DEFAULT '';
l_line LONG;
l_desctbl dbms_sql.desc_tab;
v_sqlerrm VARCHAR2(32000);
l_mode CHAR(1) := 'w';
BEGIN
IF p_mode NOT IN('w', 'a') THEN
l_mode := 'w';
ELSE
l_mode := p_mode;
END IF;

l_output := utl_file.fopen(p_dir,p_filename,l_mode,p_max_linesize);
dbms_sql.parse(l_thecursor,p_query,dbms_sql.native);
dbms_sql.describe_columns(l_thecursor,l_colcnt,l_desctbl);

FOR i IN 1 .. l_colcnt LOOP
dbms_sql.define_column(l_thecursor,i,l_columnvalue,4000);

IF (l_desctbl(i).col_type = 2) THEN
l_desctbl(i).col_max_len := l_desctbl(i).col_precision + 2;
ELSIF (l_desctbl(i).col_type = 12) THEN
l_desctbl(i).col_max_len := 20;
ELSIF (l_desctbl(i).col_type = 8) THEN
l_desctbl(i).col_max_len := 2000;
END IF;

IF p_headers THEN
utl_file.put(l_output, l_separator || l_desctbl(i).col_name);
l_separator := p_separator;
END IF;
END LOOP;

IF p_trailing_separator THEN
utl_file.put(l_output, l_separator);
END IF;

IF p_headers THEN
utl_file.new_line(l_output);
END IF;

l_status := dbms_sql.EXECUTE(l_thecursor);

LOOP
EXIT WHEN(dbms_sql.fetch_rows(l_thecursor) <= 0);
l_line := NULL;
l_separator := '';

FOR i IN 1 .. l_colcnt LOOP
dbms_sql.COLUMN_VALUE(l_thecursor, i, l_columnvalue);

IF NVL(INSTR(l_columnvalue, ','), 0) = 0 THEN
NULL;
ELSE
l_columnvalue := '"' || l_columnvalue || '"';
END IF;

utl_file.put(l_output, l_separator || l_columnvalue);
l_separator := p_separator;
END LOOP;

IF p_trailing_separator THEN
utl_file.put(l_output, l_separator);
END IF;

utl_file.new_line(l_output);
l_cnt := l_cnt + 1;
END LOOP;

dbms_sql.close_cursor(l_thecursor);
utl_file.fclose(l_output);
RETURN l_cnt;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('NO_DATA_FOUND');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.invalid_path THEN
dbms_output.put_line('UTL_FILE.INVALID_PATH');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.read_error THEN
dbms_output.put_line('UTL_FILE.READ_ERROR');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.write_error THEN
dbms_output.put_line('UTL_FILE.WRITE_ERROR');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.invalid_mode THEN
dbms_output.put_line('UTL_FILE.INVALID_MODE');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.invalid_filehandle THEN
dbms_output.put_line('UTL_FILE.INVALID_FILEHANDLE');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.invalid_operation THEN
dbms_output.put_line('UTL_FILE.INVALID_OPERATION');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.internal_error THEN
dbms_output.put_line('UTL_FILE.INTERNAL_ERROR');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN utl_file.invalid_maxlinesize THEN
dbms_output.put_line('UTL_FILE.INVALID_MAXLINESIZE');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN VALUE_ERROR THEN
dbms_output.put_line('UTL_FILE.VALUE_ERROR');
utl_file.fclose(l_output);
RETURN l_cnt;
WHEN OTHERS THEN
-- hum_do.default_exception('ERROR in dump_csv : ');
utl_file.fclose(l_output);
RETURN l_cnt;
END dump_flatfile;
------------------------------------------------------------------------
-- Return the next email address in the list of email addresses, separated
-- by either a "," or a ";". The format of mailbox may be in one of these:
-- someone@some-domain
-- "Someone at some domain" <someone@some-domain>
-- Someone at some domain <someone@some-domain>
FUNCTION get_mail_address(addr_list IN OUT VARCHAR2) RETURN VARCHAR2
IS
addr VARCHAR2(256);
i PLS_INTEGER;
FUNCTION lookup_unquoted_char(str IN VARCHAR2,chrs IN VARCHAR2) RETURN PLS_INTEGER
AS
c VARCHAR2(5);
i PLS_INTEGER;
len PLS_INTEGER;
inside_quote BOOLEAN;
BEGIN
inside_quote := FALSE;
i := 1;
len := LENGTH(str);

WHILE(i <= len) LOOP
c := SUBSTR(str, i, 1);

IF (inside_quote) THEN
IF (c = '"') THEN
inside_quote := FALSE;
ELSIF (c = '\') THEN
i := i + 1;
END IF;
GOTO next_char;
END IF;

IF (c = '"') THEN
inside_quote := TRUE;
GOTO next_char;
END IF;

IF (INSTR(chrs, c) >= 1) THEN
RETURN i;
END IF;

<<next_char>>
i := i + 1;
END LOOP;

RETURN 0;
END;
BEGIN
addr_list := LTRIM(addr_list);
i := lookup_unquoted_char(addr_list, ',;');

IF (i >= 1) THEN
addr := SUBSTR(addr_list,1,i - 1);
addr_list := SUBSTR(addr_list, i + 1);
ELSE
addr := addr_list;
addr_list := '';
END IF;

i := lookup_unquoted_char(addr, '<');

IF (i >= 1) THEN
addr := SUBSTR(addr, i + 1);
i := INSTR(addr, '>');

IF (i >= 1) THEN
addr := SUBSTR(addr, 1, i - 1);
END IF;
END IF;

RETURN addr;
END get_mail_address;
------------------------------------------------------------------------
FUNCTION smtp_command(
command IN VARCHAR2,
ok IN VARCHAR2 DEFAULT '250',
code OUT VARCHAR2) RETURN BOOLEAN
IS
response VARCHAR2(3);
p_output_message VARCHAR2(355);
len PLS_INTEGER;
conn utl_tcp.connection;
----PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
len := utl_tcp.write_line(conn, command);
p_output_message := SUBSTR(utl_tcp.get_line(conn, TRUE),1,355);
response := SUBSTR(p_output_message,1,3);
p_output_message :=SUBSTR(command || ' - ' || p_output_message,1,355);
IF (response <> ok) THEN
code := response;
RETURN FALSE;
ELSE
code := response;
RETURN TRUE;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_output_message := SQLCODE || ' - ' || SQLERRM;
code := p_output_message;
RETURN FALSE;
END smtp_command;
------------------------------------------------------------------------
FUNCTION query_server RETURN BOOLEAN
IS
p_output_message VARCHAR2(255);
err_noop EXCEPTION; -- SMTP code 250 not received
err_server_reject EXCEPTION;
conn utl_tcp.connection;
BEGIN
conn := utl_tcp.open_connection(v_smtp_server,v_smtp_server_port);
rc := utl_tcp.write_line(conn, 'HELO ' || v_smtp_domain);
p_output_message := utl_tcp.get_line(conn, TRUE);

IF SUBSTR(p_output_message, 1, 3) = '421' THEN
RAISE err_server_reject;
END IF;

rc := utl_tcp.write_line(conn, 'EHLO mahadiscom.in');
p_output_message := utl_tcp.get_line(conn, TRUE);

IF SUBSTR(p_output_message, 1, 3) = '421' THEN
RAISE err_server_reject;
END IF;

rc := utl_tcp.write_line(conn, 'NOOP');
p_output_message := utl_tcp.get_line(conn, TRUE);
IF SUBSTR(p_output_message, 1, 3) <> '250' THEN
RAISE err_noop;
END IF;
rc := utl_tcp.write_line(conn, 'QUIT');
BEGIN
FOR i_idx IN 1 .. 100 LOOP
p_output_message := utl_tcp.get_line(conn, TRUE);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
utl_tcp.close_connection(conn); ----- CLOSE SMTP PORT CONNECTION
RETURN TRUE;
EXCEPTION
WHEN err_server_reject THEN
print_output( 'ERROR -'
|| ' Server Rejected Connection ::'
|| ' SERVER_MSG := '
|| p_output_message);
RETURN FALSE;
WHEN err_noop THEN
print_output( 'ERROR -'
|| ' NOOP Check Failed ::'
|| ' SERVER_MSG := '
|| p_output_message);
utl_tcp.close_connection(conn); ----- CLOSE SMTP PORT CONNECTION
RETURN FALSE;
END query_server;
------------------------------------------------------------------------
FUNCTION get_local_binary_data(p_dir IN VARCHAR2,p_file IN VARCHAR2) RETURN BLOB
IS
l_bfile BFILE;
l_data BLOB;
l_dbdir VARCHAR2(200) := p_dir;
BEGIN
dbms_lob.createtemporary(l_data,TRUE,dbms_lob.CALL);
l_bfile := BFILENAME(l_dbdir, p_file);
dbms_lob.fileopen(l_bfile, dbms_lob.file_readonly);
dbms_lob.loadfromfile(l_data,l_bfile,dbms_lob.getlength(l_bfile));
dbms_lob.fileclose(l_bfile);
RETURN l_data;
EXCEPTION
WHEN OTHERS THEN
print_output( 'Error during GET_LOCAL_BINARY_DATA :: '
|| SQLCODE
|| ' - '
|| SQLERRM);
dbms_lob.fileclose(l_bfile);
RAISE;
END get_local_binary_data;

------------------------------------------------------------------------
PROCEDURE attach_base64(
conn IN OUT NOCOPY utl_tcp.connection,
DATA IN BLOB)
IS
i PLS_INTEGER;
len PLS_INTEGER;
l_result PLS_INTEGER;
l_buffer RAW(32767);
l_pos INTEGER := 1;
l_blob_len INTEGER;
l_amount BINARY_INTEGER := 32767;
req utl_http.req;
resp utl_http.resp;
pdata RAW(200);
BEGIN
l_blob_len := dbms_lob.getlength(DATA);
WHILE l_pos < l_blob_len
LOOP
l_amount := max_base64_line_width;
dbms_lob.READ(DATA, l_amount, l_pos, l_buffer);
rc := utl_tcp.write_raw(conn, utl_encode.base64_encode(l_buffer));
utl_tcp.FLUSH(conn);
l_pos := l_pos + max_base64_line_width;
rc := utl_tcp.write_line(conn, crlf);
END LOOP;
END attach_base64;
------------------------------------------------------------------------
PROCEDURE sendmail(
from_name VARCHAR2,
to_name VARCHAR2,
cc_name VARCHAR2 DEFAULT NULL,
bcc_name VARCHAR2 DEFAULT NULL,
subject VARCHAR2,
MESSAGE LONG,
dir VARCHAR2 DEFAULT NULL,
filename VARCHAR2 DEFAULT NULL
)
IS
pos PLS_INTEGER := 1;
bytes_o_data CONSTANT PLS_INTEGER := 32767;
offset PLS_INTEGER := bytes_o_data;
msg_length CONSTANT PLS_INTEGER := length(MESSAGE);
v_line VARCHAR2(32767);
i BINARY_INTEGER;
v_slash_pos NUMBER;
my_recipients VARCHAR2(32767);
p_recipient_count PLS_INTEGER := 0;
p_output_message VARCHAR2(5000);
err_server_reject EXCEPTION;
err_message_send EXCEPTION;
err_end_of_input EXCEPTION;
l_result PLS_INTEGER;
l_buffer_b RAW(32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
l_blob BLOB;
g_debug BOOLEAN := TRUE;
i_base64 PLS_INTEGER;
len_base64 PLS_INTEGER;
conn utl_tcp.connection;
BEGIN
conn :=utl_tcp.open_connection(v_smtp_server,v_smtp_server_port);
dbms_lock.sleep(0.5);
dbms_output.put_line('----before Helo ---------');
rc := utl_tcp.write_line(conn, 'HELO ' || v_smtp_domain);
dbms_lock.sleep(0.5);
dbms_output.put_line('----after Helo ---------');
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_lock.sleep(0.5);
dbms_output.put_line(p_output_message);
IF SUBSTR(p_output_message, 1, 3) = '421' THEN
RAISE err_server_reject;
ELSE
dbms_output.put_line('----before ehlo ---------');
rc := utl_tcp.write_line(conn, 'EHLO '|| v_smtp_domain);
dbms_lock.sleep(0.5);
--p_output_message := utl_tcp.get_line(conn, TRUE);
--dbms_output.put_line(p_output_message);
dbms_output.put_line('----before mail from ---------');
rc := utl_tcp.write_line(conn, 'MAIL FROM: ' || from_name);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before RCPT TO: ---------');
my_recipients := to_name;
WHILE(my_recipients IS NOT NULL) LOOP
BEGIN
rc :=utl_tcp.write_line(conn, 'RCPT TO: '|| get_mail_address(my_recipients));
p_recipient_count := p_recipient_count + 1;
END;
END LOOP;
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before CC ---------');

my_recipients := cc_name;
WHILE(my_recipients IS NOT NULL) LOOP
BEGIN
rc :=utl_tcp.write_line(conn, 'RCPT TO: '|| get_mail_address(my_recipients));
p_recipient_count := p_recipient_count + 1;
END;
END LOOP;
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before BCC ---------');

my_recipients := bcc_name;
WHILE(my_recipients IS NOT NULL) LOOP
BEGIN
rc := utl_tcp.write_line(conn, 'RCPT TO: '|| get_mail_address(my_recipients));
p_recipient_count := p_recipient_count + 1;
END;
END LOOP;
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before DATA ---------');
rc := utl_tcp.write_line(conn, 'DATA');
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before Date ---------');
rc := utl_tcp.write_line(conn, 'Date: '|| to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss'));
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before from ---------');
rc := utl_tcp.write_line(conn, 'From: ' || from_name);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before To ---------');
rc := utl_tcp.write_line(conn, 'To: ' || to_name);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
IF cc_name IS NOT NULL THEN
dbms_output.put_line('----before CC ---------');
rc := utl_tcp.write_line(conn, 'Cc: ' || cc_name);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
END IF;

IF bcc_name IS NOT NULL THEN
dbms_output.put_line('----before BCC ---------');
rc := utl_tcp.write_line(conn, 'Bcc: ' || bcc_name);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);

END IF;
dbms_output.put_line('----before SUBJECT ---------');
rc := utl_tcp.write_line(conn, 'Subject: ' || subject);
dbms_lock.sleep(0.5);
p_output_message := utl_tcp.get_line(conn, TRUE);
dbms_output.put_line(p_output_message);
dbms_output.put_line('----before MIME ---------');
rc := utl_tcp.write_line(conn, 'Mime-Version: 1.0');
--dbms_lock.sleep(0.5);
--p_output_message := utl_tcp.get_line(conn, TRUE);
--dbms_output.put_line(p_output_message);
dbms_output.put_line('----before Content ---------');
rc := utl_tcp.write_line(conn, 'Content-Type: multipart/mixed;'); ----- INDICATES THAT THE BODY CONSISTS OF MORE THAN ONE PART
dbms_output.put_line('----before boundary ---------');
rc := utl_tcp.write_line(conn, ' boundary="-----SECBOUND"'); ----- SEPERATOR USED TO SEPERATE THE BODY PARTS
rc := utl_tcp.write_line(conn, utl_tcp.crlf); ----- DO NOT REMOVE THIS BLANK LINE - PART OF MIME STANDARD
rc := utl_tcp.write_line(conn, '-------SECBOUND');
rc := utl_tcp.write_line(conn, 'Content-Type: text/html'); ----- 1ST BODY PART. EMAIL TEXT MESSAGE
rc := utl_tcp.write_line(conn, 'Content-Transfer-Encoding: 7bit');
rc := utl_tcp.write_line(conn, utl_tcp.crlf);
dbms_output.put_line('----before body ---------');
WHILE pos < msg_length LOOP
rc :=utl_tcp.write_text(conn, SUBSTR(MESSAGE,pos,max_base64_line_width));
pos := pos + max_base64_line_width;
--offset := pos-offset;
END LOOP;
rc := utl_tcp.write_line(conn, utl_tcp.crlf);
IF filename IS NOT NULL THEN
BEGIN
l_blob := get_local_binary_data(dir,filename);
dbms_output.put_line('----before file ---------');
rc := utl_tcp.write_line(conn, '-------SECBOUND');
rc := utl_tcp.write_line(conn,'Content-Type: application/octet-stream; name="'|| filename|| '"');
rc := utl_tcp.write_line(conn,'Content-Disposition: attachment; filename="'|| filename|| '"');
rc := utl_tcp.write_line(conn, 'Content-Transfer-Encoding: base64');
rc := utl_tcp.write_line(conn, utl_tcp.crlf);
attach_base64(conn,l_blob);
dbms_lob.freetemporary(l_blob);
EXCEPTION
WHEN OTHERS THEN
p_output_message := 'Error in attaching file ' || filename
|| ' :: '|| SQLCODE|| ' - '|| SQLERRM;
dbms_lob.freetemporary(l_blob);
RAISE err_message_send;
END;
END IF;

rc := utl_tcp.write_line(conn, '-------SECBOUND--');
rc := utl_tcp.write_line(conn, utl_tcp.crlf);
rc := utl_tcp.write_line(conn, '.'); ----- EMAIL MESSAGE BODY END
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
rc := utl_tcp.write_line(conn, 'QUIT'); ----- ENDS EMAIL TRANSACTION
dbms_output.put_line(utl_tcp.get_line(conn, TRUE));
BEGIN
FOR i_idx IN 1 .. 100 LOOP
p_output_message := utl_tcp.get_line(conn, TRUE);

END LOOP;
EXCEPTION
WHEN OTHERS THEN
NULL;

END;
END IF; -- err_server_reject

utl_tcp.close_connection(conn); ----- CLOSE SMTP PORT CONNECTION
EXCEPTION
WHEN err_message_send
THEN
print_output( CHR(50)
|| CHR(50)
|| 'ERROR -'
|| ' Message was not submitted for delivery');
print_output(' [FROM_NAME := ' || from_name || '] ');
print_output(' [TO_NAME := ' || to_name || '] ');
print_output(' [CC_NAME := ' || cc_name || '] ');
print_output(' [BCC_NAME := ' || bcc_name || '] ');
print_output(' [SUBJECT := ' || subject || '] ');
print_output(' SERVER_MSG := ' || p_output_message);
utl_tcp.close_connection(conn); ----- CLOSE SMTP PORT CONNECTION
WHEN err_server_reject
THEN
print_output( CHR(50)
|| CHR(50)
|| 'ERROR -'
|| ' Server Rejected Email');
print_output(' [FROM_NAME := ' || from_name || '] ');
print_output(' [TO_NAME := ' || to_name || '] ');
print_output(' [CC_NAME := ' || cc_name || '] ');
print_output(' [BCC_NAME := ' || bcc_name || '] ');
print_output(' [SUBJECT := ' || subject || '] ');
print_output(' SERVER_MSG := ' || p_output_message);
WHEN OTHERS
THEN
print_output( CHR(50)
|| CHR(50)
|| 'ERROR :: '
|| SQLCODE
|| ' - '
|| SQLERRM);
print_output(' [FROM_NAME := ' || from_name || '] ');
print_output(' [TO_NAME := ' || to_name || '] ');
print_output(' [CC_NAME := ' || cc_name || '] ');
print_output(' [BCC_NAME := ' || bcc_name || '] ');
print_output(' [SUBJECT := ' || subject || '] ');
print_output(' SERVER_MSG := ' || p_output_message);
END sendmail;
END;
/

"---------Data coming in E-mail is invalid.......please suggest for the same"

Mail Body:-Cc: rkgupta@tatapower.com; mdsalvi@tatapower.com; msinha@tatapower.com; tshelke@tatapower.com; tl-santosh@tatapower.com; Bikash.Samal@tatapower.com; amishra@tatapower.com; rajesh.rajgurav@tatapower.com; vbpatil@tatapower.com; madhav.shinde@tatapower.com; cmc-lokesh@tatapower.com;

Subject: New Connection [ CENTAUR MERCANTILE PVT LTD - Request for Execution.]
Mime-Version: 1.0
Content-Type: multipart/mixed;
boundary="-----SECBOUND"


-------SECBOUND
Content-Type: text/html
Content-Transfer-Encoding: 7bit


Request for New Connection as per the details below :<BR/><br><TABLE><TR><TH valign='top' align='left' width='500px' >Name :</TH><TD> CENTAUR MERCANTILE PVT LTD </TD></TR><TR><TH valign='top' align='left' width='100px' >RequestNo. :</TH><TD> 12NC016083</TD></TR><TR><TH valign='top' align='left' width='100px' >CDDNo. :</TH><TD> </TD></TR><TR><TH valign='top' align='left' width='150px' >Business Partner No. : </TH><TD>851485</TD></TR><TR><TH valign='top' align='left' width='150px' >Address : </TH><TD>OFFICE NO. -208 A WING, BUILDING NAME -BOOMERANG EQUITY BUSSI PARK CTS NO 4 ETC, KURLA STREET -CHANDIVALI FARM ROAD LANDMARK NEAR HINDUSTAN TRANSMISSION COMPOUND CHANDIVALI (SAKI NAKA), Mumbai, Maharashtra, PIN ID - 400072 </TD></TR><TR><TH valign='top' align='left' width='150px' >Contact No: </TH><TD>67266666</TD></TR><TR><TH valign='top' align='left' colspan=2 align=left >Load Details :</TH></TR><TR><TD valign='top' align='left' width='150px' >Supply(Voltage) : </TD><TD>LT THREE PHASE</TD></TR><TR><TD valign='top' align='left' width='150px' >Supply(Category) : </TD><TD>COMMERCIAL</TD></TR><TR><TD valign='top' align='left' width='150px'>Sanctioned(Load)KW:</TD><TD>42.00</TD></TR><TR><TH valign='top' align='left' width='150px' >Electrical Contractor Contact No: </TH><TD> TEL NO :26114455<BR> MOBILE NO :9820309199 </TD></TR></TABLE><br>Technical Feasibility report attached for your reference.<BR/>We request you to execute the work and Confirm. <BR/><BR/>It is requested to release the supply before the expiry of SOP date 05th February 2012

-------SECBOUND
Content-Type: application/octet-stream; name="Application_for_execution_12NC016083.pdf"
Content-Disposition: attachment; filename="Application_for_execution_12NC016083.pdf"
Content-Transfer-Encoding: base64


JVBERi0xLjIgCiXi48/TIAoxIDAgb2JqIAo8PCAKL1R5cGUgL0NhdGFsb2cgCi9Q
YWdlcyAyIDAg


Tom Kyte
January 10, 2012 - 10:26 pm UTC

I hate your code:

search for "I hate your code" in my homepage to see why...


   EXCEPTION
      WHEN OTHERS
      THEN
         NULL;               -- Ignore errors... protect buffer overflow's etc.
   END print_output;

      WHEN OTHERS THEN
         utl_file.fclose(l_output);
         RETURN l_cnt;
   END dump_flatfile;

  EXCEPTION
      WHEN OTHERS THEN
         p_output_message := SQLCODE || ' - ' || SQLERRM;
         code := p_output_message;
         RETURN FALSE;
   END smtp_command;

      BEGIN
         FOR i_idx IN 1 .. 100 LOOP
            p_output_message := utl_tcp.get_line(conn, TRUE);            
         END LOOP;
        EXCEPTION
          WHEN OTHERS THEN
               NULL;            
      END;

         EXCEPTION
            WHEN OTHERS THEN                      
                  NULL;
               
         END;

      WHEN OTHERS
      THEN
         print_output(   CHR(50)
                        || CHR(50)
                        || 'ERROR :: '
                        || SQLCODE
                        || ' - '
                        || SQLERRM);
         print_output(' [FROM_NAME := ' || from_name || '] ');
         print_output(' [TO_NAME := ' || to_name || '] ');
         print_output(' [CC_NAME := ' || cc_name || '] ');
         print_output(' [BCC_NAME := ' || bcc_name || '] ');
         print_output(' [SUBJECT := ' || subject || '] ');
         print_output(' SERVER_MSG := ' || p_output_message);
   END sendmail;


I truly do. Other than that, I don't have anything to say since there doesn't seem to be a question here.


that is some scary stuff.

Subject line

Rits, February 12, 2012 - 11:42 pm UTC

Hey Tom, nice article there.

I was trying to do a multilingual HTML email program and managed to get non Roman characters (Japanese, Korean, Chinese) working by writing raw data to smtp and using a tcp connection to smtp server. The body comes out nicely in Japanese/Korean/Chinese characters.

I was not so successful with the subject line though. Do you have any ideas on how to get the subject line coded so that asian language is displayed correctly? FYI I tried converting subject line to raw data and then used UTL_CONVERT to convert it into UTF16/UTF8 but does not help.
Tom Kyte
February 13, 2012 - 8:13 am UTC

Sorry - you'll have to search around for specs on how this is done, not really a database question at all - more a question about how email deals with encoding this.

I don't know offhand.

Subject line

Rits, February 12, 2012 - 11:43 pm UTC

Hey Tom, nice article there.

I was trying to do a multilingual HTML email program and managed to get non Roman characters (Japanese, Korean, Chinese) working by writing raw data to smtp and using a tcp connection to smtp server. The body comes out nicely in Japanese/Korean/Chinese characters.

I was not so successful with the subject line though. Do you have any ideas on how to get the subject line coded so that asian language is displayed correctly? FYI I tried converting subject line to raw data and then used UTL_CONVERT to convert it into UTF16/UTF8 but does not help.

The code I am using is below


PROCEDURE html_email(p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null) is
l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
l_connection utl_smtp.connection;
l_connection_tcp utl_tcp.connection;
l_body_html clob := empty_clob; --This LOB will be the email message
l_offset number;
l_ammount number;
l_temp varchar2(32767) default null;
begin
--l_connection := utl_smtp.open_connection('smtp.cochlear.com', '25');
l_connection_tcp:=utl_tcp.open_connection('smtp.XXXX.com', '25','AL32UTF8');
--utl_smtp.helo(l_connection, p_smtp_hostname);
l_connection.private_tcp_con := l_connection_tcp;
utl_smtp.helo(l_connection, 'smtp.XXXX.com');
utl_smtp.mail(l_connection, p_from);
utl_smtp.rcpt(l_connection, p_to);

l_temp := l_temp || 'MIME-Version: 1.0' || chr(13) || chr(10);
l_temp := l_temp || 'To: ' || p_to || chr(13) || chr(10);
l_temp := l_temp || 'From: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Subject: ' || p_subject || chr(13) || chr(10);
l_temp := l_temp || 'Reply-To: ' || p_from || chr(13) || chr(10);
l_temp := l_temp || 'Content-Type: multipart/alternative; boundary=' ||
chr(34) || l_boundary || chr(34) || chr(13) || chr(10);

----------------------------------------------------
-- Write the headers
dbms_lob.createtemporary(l_body_html, false, 10);
dbms_lob.write(l_body_html, length(l_temp), 1, l_temp);

----------------------------------------------------
-- Write the text boundary
l_offset := dbms_lob.getlength(l_body_html) + 1;
l_temp := '--' || l_boundary || chr(13) || chr(10);
l_temp := l_temp || 'content-type: text/plain; charset=utf-8' ||
chr(13) || chr(10) || chr(13) || chr(10);
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Write the plain text portion of the email
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(p_text), l_offset, p_text);

----------------------------------------------------
-- Write the HTML boundary
l_temp := chr(13) || chr(10) || chr(13) || chr(10) || '--' ||
l_boundary || chr(13) || chr(10);
l_temp := l_temp ||'content-type:text/html;charset=utf-8' || chr(13) || chr(10) ||
chr(13) || chr(10);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Write the HTML portion of the message
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(p_html), l_offset, p_html);

----------------------------------------------------
-- Write the final html boundary
l_temp := chr(13) || chr(10) || '--' || l_boundary || '--' || chr(13);
l_offset := dbms_lob.getlength(l_body_html) + 1;
dbms_lob.write(l_body_html, length(l_temp), l_offset, l_temp);

----------------------------------------------------
-- Send the email in 1900 byte chunks to UTL_TCP
l_offset := 1;
l_ammount := 1900;
utl_smtp.open_data(l_connection);
while l_offset < dbms_lob.getlength(l_body_html) loop
utl_smtp.write_raw_data(l_connection, utl_raw.cast_to_raw(dbms_lob.substr(l_body_html, l_ammount, l_offset)));
l_offset := l_offset + l_ammount;
l_ammount := least(1900, dbms_lob.getlength(l_body_html) - l_ammount);
end loop;
utl_smtp.close_data(l_connection);
utl_tcp.close_connection(l_connection_tcp);
dbms_lob.freetemporary(l_body_html);
end html_email;

Image from oracle database table on my email body and not as email attachment

Rijo Rajan, August 06, 2012 - 4:58 am UTC

Hi, Tom

I have a question and I need your help.
I "create" a html file according your specifications, OK,
but if I insert a tag
l_body:=l_body||'<IMG src="c:\logowin.gif">'
I will
not receive this image on my email. Can you
help me?
I found your repsonse to a similar quetion as

"you need to have it accessible on a webserver.
you are just sending a webpage, an html page. It can have URLs in it, the url is resolved on the
client. unless that client has logowin.gif on c:\ -- it'll never find it.So, put logowin.gif on a webserver and use "


Whether is it possible to have that image from an oracle database table on my email body without using some URL?

Please note that I want it in the email body and not as an email attachment.

Regards
Rijo




Tom Kyte
August 17, 2012 - 12:02 pm UTC

does the person receiving this

a) run windows
b) have a c:\ drive
c) and a file logowin.gif

in it.


Inline iamge to be shown in email

Sam, October 04, 2012 - 8:13 am UTC

Hi Tom,

I also do have the same query regarding how to send a inline image in email through utl_smtp. I don't have a webserver and all my images are stored in a Oracle table in BLOB field.

Please let me know how to implement this.

Thanks

Send mail

Osama, March 17, 2013 - 5:47 am UTC

Hello Tom,

I had created a Procedure to insert a data into an empty table then i created a Job to execute this procedure in a daily bases, my question is how can i send the inserted data in this empty table to my email (gmail) as a daily report.

Regards
Tom Kyte
March 25, 2013 - 3:08 pm UTC

http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_mail.htm#ARPLS71198

write a procedure that queries the data, formats it, and utl_mails it to you.

Not wokring on Outlook 2010

Francois, June 26, 2013 - 7:37 am UTC

When I call it with

declare
begin
xxpay_smtp.html_email
(
p_to => 'to@email.com', -- in varchar2,
p_from => 'from@email.com', -- in varchar2,
p_subject => 'Subject FD', -- in varchar2,
p_text => 'Body FD', -- in varchar2 default null,
p_html => '<html>
<head>
<title>some subject</title>
</head>
<body>
Your Html email message here.
</body>
</html>', -- in varchar2 default null,
p_smtp_hostname => 'smtp.com', -- in varchar2,
p_smtp_portnum => '25' -- in varchar2
);
end;It comes back with an email body that looks like this:

Body FD

--a1b2c3d4e3f2g1
content-type: text/html;

<html>
<head>
<title>some subject</title>
</head>
<body>
Your Html email message here.
</body>
</html>
--a1b2c3d4e3f2g1--I am using Outlook 2010. Does anyone know where the Ask Tom example goes wrong, or what I am doing wrong?

Thanks

EXCELLENT

DRAGON81, August 16, 2013 - 10:14 pm UTC

THANKS!!! GREAT JOB!!THIS SAVE ME ALOT OF TIME

Pradeep Rayapudi, September 13, 2013 - 8:55 pm UTC

Thank you!!!!!!! Very much useful......

Mailing format

rajesh, November 15, 2013 - 11:20 am UTC

when i try to send mail using utl_smtp.write_data.We are getting mails in outlook for one user and for another user its displaying as blank message.

Is there any problem with outlook version can any one help me out of this

very helpfull post.

Muhammad Jawad, April 15, 2014 - 8:39 am UTC

thanks TOM for writing such helpful article, I want to record some feedback in the form of "Yes" "NO" in my DB depending on the reply of email user gives.... can i use a Hyperlink in my email which access my DB and set a status... Is this possible? or any way around please guide?
Tom Kyte
April 16, 2014 - 4:20 pm UTC

sure, not only possible but trivial.

just "do it"? you see how to include HTML in the email, just include a link to a URL that you write some code for on your application server to do something.


Just have two urls that you could put into a url bar in your browser, one updates a row to no, the other to yes - and then they return a web page that says "thanks" or something

Different databases, different results

Howard, September 09, 2014 - 9:11 pm UTC

Hi Tom,

This bit of code is exactly what I needed to send our Customer Service Reps notifications of order statuses.

Each of our facilities runs on a separate Oracle database for their ERPs. All of these are running this bit of code to email them the notices. However, one of them is getting a different results format than the others. While almost all of them get something like this...

The following orders are ready to be released:
CUSTOMER_ID CUST_ORDER_ID STATUS
--------------- --------------- -------------

...the errant databases produces this for the exact same report (using the exact same code):

--a1b2c3d4e3f2g1
content-type: text/html;

The following Firmed Orders are ready to be Released:<br /><br /><font face="courier" size="1"><table border="0" style="font-family:courier;"><tr><td>CUST_ORDER_ID</td><td>STATUS</td></tr><tr><td>---------------</td><td>-------------</td></tr></table></font>
--a1b2c3d4e3f2g1--

Thoughts on why this might be happening? I'm guessing the Oracle on that database is obviously different, but I have no oversight to it. Thanks!

HTML Email with Attachement

ART, August 18, 2015 - 2:48 pm UTC

Hi Tom,
Thanks for your code to send html email,
can you help me below issues, we want send EMAIL IN html format and also attached file over the email..

html_email(
p_to in varchar2,
p_from in varchar2,
p_subject in varchar2,
p_text in varchar2 default null,
p_html in varchar2 default null,
p_smtp_hostname in varchar2,
p_smtp_portnum in varchar2,
'SM_UPD_BAL_AUG.PDF'
);

Take a look a utl_mime

Michael, November 25, 2015 - 11:56 am UTC

I have created a new solution for creating HTML mails (including images) and attached files called utl_mime.
It's free and a simple PL/SQL (and PL/SQL only) package.

Project page: http://sourceforge.net/projects/utlmime
Project Wiki: http://sourceforge.net/p/utlmime/wiki


A reader, April 11, 2017 - 6:20 am UTC


(Exceptional) code for mail generation

Prasad, April 11, 2017 - 6:49 am UTC

Thanks for code. Worked flawlessly at first instant.
You saved my day TOM :-)
Thnx..

It does not seem to work anymore in 12g

Bruno Magnani, November 21, 2017 - 7:14 pm UTC

Hi,

Thank you, it works perfectly in Oracle 11.2.0.1, after setting proper ACL privileges.

In Oracle 12g (and 11.2.0.4 too) it doesn't work anymore.
The line "utl_smtp.close_data(l_connection)" at the end of the procedure raises the error "ORA-29278: SMTP transient error: 421 Service not available".
I launched exactly the same procedure with the same arguments, with an user with the same privileges using the same SMTP server as I did with Oracle 11.2.0.1 successfully.

It's a bug, or maybe Oracle 12g needs some more authorizations?

Bruno

Bruno Magnani, November 22, 2017 - 3:58 pm UTC

I apologize for my previous review.
The problem was not dependent on the version of Oracle, but on the SMTP server that allowed HTML email only from certain addresses.

Bruno
Connor McDonald
November 23, 2017 - 12:42 am UTC

Thanks for getting back to us

Manjusree Bhaskar, October 11, 2019 - 9:45 pm UTC

Since DB upgrading to 11.2.04 from 8, the programs seem to be failing with ORA-29279: SMTP permanent error: 554 5.6.0 Invalid message content when emails are sent from one sender and in other cases, its a successful completion but the email message body is blank

IS this similar to anyone's issues?
Connor McDonald
October 21, 2019 - 11:57 am UTC

Sorry, I havent seen that. Perhaps this will help

https://asktom.oracle.com/pls/apex/asktom.search?tag=ora-29279-smtp-permanent-error-502-command-not-implemented-while-executing-a-mail-proc

The other source of these is character issues in the initial commands (eg authentication etc) - mail is very particular about the carriage returns etc.

Issue with HTML table to be printed over Outlook

Pratik Wagh, March 01, 2021 - 5:19 pm UTC

After creating a stored procedure to create a html file as mentioned in below snippet as :-

prm_message := '<html></html>' [any html code]

UTL_SMTP.write_data (mmy_mail_conn, '--' || l_boundary || '--' || mmy_crlf);
UTL_SMTP.write_data (mmy_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"'||mmy_crlf ||mmy_crlf);
UTL_SMTP.write_data (mmy_mail_conn, prm_message||mmy_crlf ||mmy_crlf);

Then output for this code is :-
The html code gets printed at the outlook as :-
<html></html>

Expected Output is :-
the html code must print the data as executed by html compiler
Connor McDonald
March 02, 2021 - 1:47 am UTC

If you're struggling to get the format *exactly* right, perhaps look at using the apex_mail package to do the heavy lifting for you

    apex_mail.send(
      p_to        => ...,
      p_from      =>...,
      p_body      => l_body,
      p_body_html => l_email_html,
      p_subj      => ...


Satish, May 25, 2021 - 11:02 pm UTC

The emails are sent intermittently when we run the program/batch for 50+ or 100+ emails (using UTL_SMTP calls). There are no errors on the oracle. If we resend the email it will be received without any issue. How we can root cause the issue. Why there is intermittent failure. Is there any alternate option.

My code from this discussion

patrick, August 27, 2021 - 12:23 pm UTC

I fixed the problem of not recognizing the multipart with Microsoft messaging (adding a CRLR), automatically translated the html message into plain text, managed the cc and bcc.

CREATE OR REPLACE procedure SEND_MAIL_HTML(

    -- =============================================================================================
    -- SAMPLES : 
    -- 
 -- BEGIN
    --    SEND_MAIL_HTML(
    --      recipients => 'destinataire@domaine.fr',
    --      bcc        => 'copie_to@domaine.com', 
    --      subject    => 'subject of mail',
    --      message    => '<html><body>Hello<b>Word !</b></body></html>'
    --    );
 -- END;
    --
    -- =============================================================================================
    
    recipients in Varchar2, 
    subject    in Varchar2, 
    message    in Varchar2,
    sender     in Varchar2 default 'noreply@bigard.fr',
    cc         in Varchar2 default NULL,
    bcc        in Varchar2 default NULL 
)is
    l_boundary varchar2(255) default 'a1b2c3d4e3f2g1';
    l_connection utl_smtp.connection;
    l_body_html clob := empty_clob; --This LOB will be the email message
    l_offset number;
    l_ammount number;
    l_temp varchar2(32767) default null;
    
    begin

        l_connection := utl_smtp.open_connection( '01VW-SMTPREL01.bigard.local' );
        utl_smtp.helo( l_connection, 'bigard.fr' );
        utl_smtp.mail( l_connection, sender);
        utl_smtp.rcpt( l_connection, recipients );

        if cc  is not null then utl_smtp.rcpt( l_connection, cc);  end if;
        if bcc is not null then utl_smtp.rcpt( l_connection, bcc); end if;

        ----------------------------------------------------
        -- Write the headers (for display in mail)

        l_temp := 'MIME-Version: 1.0'         || utl_tcp.CRLF
               || 'To: '        || recipients || utl_tcp.CRLF
               || 'From: '      || sender     || utl_tcp.CRLF
               || case when cc  is not null then 'Cc: '  || cc  || utl_tcp.CRLF End
               || 'Subject: '   || subject    || utl_tcp.CRLF
               || 'Reply-To: '  || sender     || utl_tcp.CRLF
               || 'Content-Type: multipart/alternative; boundary=' || chr(34) || l_boundary || chr(34) || utl_tcp.CRLF || utl_tcp.CRLF;

        dbms_lob.createtemporary( l_body_html, false, 10 );
        dbms_lob.write(l_body_html,length(l_temp),1,l_temp);


        ----------------------------------------------------
        -- Write the text boundary (for plain text only messagerie)

        l_offset := dbms_lob.getlength(l_body_html) + 1;
        l_temp := '--' || l_boundary || utl_tcp.CRLF || 'content-type: text/plain; charset=us-ascii' || utl_tcp.CRLF || utl_tcp.CRLF;
        dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

        l_temp := REGEXP_REPLACE(REGEXP_REPLACE(message,'<br>',utl_tcp.CRLF,1,0,'i'), '<[^>]*>',''); -- suppression des balises <xxxx> sauf les <br> qui sont converti en CRLF
        l_offset := dbms_lob.getlength(l_body_html) + 1;
        dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

        ----------------------------------------------------
        -- Write the HTML boundary

        l_temp := utl_tcp.CRLF || utl_tcp.CRLF || '--' || l_boundary || utl_tcp.CRLF || 'content-type: text/html;' || utl_tcp.CRLF || utl_tcp.CRLF;
        l_offset := dbms_lob.getlength(l_body_html) + 1;
        dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

        l_offset := dbms_lob.getlength(l_body_html) + 1;
        dbms_lob.write(l_body_html,length(message),l_offset,message);

        ----------------------------------------------------
        -- Write the final html boundary

        l_temp := utl_tcp.CRLF || '--' || l_boundary || '--' || chr(13);
        l_offset := dbms_lob.getlength(l_body_html) + 1;
        dbms_lob.write(l_body_html,length(l_temp),l_offset,l_temp);

        ----------------------------------------------------
        -- Send the email in 1900 byte chunks to UTL_SMTP
        l_offset := 1;
        l_ammount := 1900;
        
        utl_smtp.open_data(l_connection);
        while l_offset < dbms_lob.getlength(l_body_html) loop
            utl_smtp.write_data(l_connection, dbms_lob.substr(l_body_html,l_ammount,l_offset));
            l_offset := l_offset + l_ammount ;
            l_ammount := least(1900,dbms_lob.getlength(l_body_html) - l_ammount);
        end loop;
        
        utl_smtp.close_data(l_connection);
        utl_smtp.quit( l_connection );
        dbms_lob.freetemporary(l_body_html);
        
            
    EXCEPTION
        WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
            BEGIN
                utl_smtp.quit(l_connection);
                dbms_lob.freetemporary(l_body_html);
            EXCEPTION
                WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
                    NULL;   -- When the SMTP server is down or unavailable, we don't
                            -- have a connection to the server. The quit call will
                            -- raise an exception that we can ignore.
            END;
            raise_application_error(-20000, 'Failed to send mail due to the following error: ' || sqlerrm);
    end;
/

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here