How to
October 29, 2001 - 10pm Central time zone
Reviewer: A reader
Hi, Tom,
How to let the db recognize the 'UTL_SMTP.CONNECTION',
which procedure we should run before using UTL_STNP?
Thanks
October 30, 2001 - 7am Central time zone
Reviewer: A reader
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
Followup October 30, 2001 - 8am Central time zone:
so?
Hang up
October 30, 2001 - 10am Central time zone
Reviewer: A reader
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...
October 30, 2001 - 11am Central time zone
Reviewer: jan van mourik from Houston, Texas
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
Followup October 30, 2001 - 1pm Central time zone:
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...
October 30, 2001 - 1pm Central time zone
Reviewer: Andrew from CA, USA
I assume attachments are done in a similar manner. An example would be great if you have one.
re: Now for attachments too...
October 30, 2001 - 1pm Central time zone
Reviewer: A reader
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
October 30, 2001 - 6pm Central time zone
Reviewer: Casey Waters from Sunnyvale, CA
You guys a great! Thanks!
Way to go!!
November 2, 2001 - 5pm Central time zone
Reviewer: patrick from usa
Way to go on this one!! after a fight with
initjvm...etc i got this to work great!!
thanks
Outstanding, thanks!
March 14, 2002 - 10am Central time zone
Reviewer: Bob Yexley from Dayton, OH USA
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.
Followup March 14, 2002 - 10am Central time zone:
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
March 14, 2002 - 11am Central time zone
Reviewer: Bob Yexley from Dayton, OH USA
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.
Followup March 14, 2002 - 12pm Central time zone:
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
March 14, 2002 - 12pm Central time zone
Reviewer: Bob Yexley from Dayton, OH USA
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
May 6, 2002 - 5am Central time zone
Reviewer: Mark Russell-Brown from GENEVA, SWITZERLAND
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;
/
Followup May 6, 2002 - 7am Central time zone:
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 !
September 15, 2002 - 11am Central time zone
Reviewer: Vikas Sharma from Delhi India
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
April 24, 2003 - 2pm Central time zone
Reviewer: mo
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,
Followup April 24, 2003 - 3pm Central time zone:
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
April 24, 2003 - 3pm Central time zone
Reviewer: mo
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
Followup April 24, 2003 - 4pm Central time zone:
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
April 26, 2003 - 2pm Central time zone
Reviewer: mo
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?
Followup April 26, 2003 - 3pm Central time zone:
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
April 28, 2003 - 11am Central time zone
Reviewer: mo
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,
Followup April 28, 2003 - 12pm Central time zone:
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 ?
May 14, 2003 - 12pm Central time zone
Reviewer: Paul Druker from Fairfax, VA USA
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 ( http://www.ietf.org/rfc/rfc0821.txt 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?
October 6, 2003 - 11am Central time zone
Reviewer: Randy from Warren, MI
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
Followup October 6, 2003 - 11am Central time zone:
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.
October 6, 2003 - 11am Central time zone
Reviewer: Randy
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);
Followup October 6, 2003 - 1pm Central time zone:
what is the length of
l_acq||'<br>'||l_eng||'<br>'||l_pa
Sending HTML using UTL_SMTP
October 16, 2003 - 6pm Central time zone
Reviewer: Ray DeBruyn Enterprise Information Systems from Ottawa, Ontario, Canada
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
October 29, 2003 - 9pm Central time zone
Reviewer: RICHARD from WASHINGTON
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.
Followup October 30, 2003 - 7am Central time zone:
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"
November 11, 2003 - 8pm Central time zone
Reviewer: Vikas Sharma from Delhi India
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
Followup November 12, 2003 - 7am Central time zone:
you can pick any size upto 32k you want to pick
December 1, 2003 - 8am Central time zone
Reviewer: Randy from Warren, MI
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!
Followup December 1, 2003 - 5pm Central time zone:
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
December 27, 2003 - 9am Central time zone
Reviewer: Vikas Sharma from Delhi India
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 => '<B> Vikas <i> Sharma',
p_smtp_hostname => 'mtlweb',
p_smtp_portnum =>25
);
end;
i get the following in my MS outlook.
--a1b2c3d4e3f2g1
content-type:text/html;
<B> Vikas <i> 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
Followup December 27, 2003 - 11am Central time zone:
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
December 28, 2003 - 4am Central time zone
Reviewer: Vikas Sharma from Delhi India
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
Followup December 28, 2003 - 11am Central time zone:
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
December 29, 2003 - 11pm Central time zone
Reviewer: Arvind from India
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
Followup December 30, 2003 - 9am Central time zone:
you'd use HTML.
like above.
work immediately after compilation
February 5, 2004 - 4am Central time zone
Reviewer: August Groh from Munich, Germany
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
February 12, 2004 - 11am Central time zone
Reviewer: Richard from Washington
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
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.
The follow-up of over 32k html content.
February 12, 2004 - 3pm Central time zone
Reviewer: Richard from WASHINGTON
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
Followup February 12, 2004 - 7pm Central time zone:
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.)
February 13, 2004 - 4pm Central time zone
Reviewer: Richard from Washington
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
February 13, 2004 - 7pm Central time zone
Reviewer: Ruben
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.
Followup February 14, 2004 - 4pm Central time zone:
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
February 18, 2004 - 8am Central time zone
Reviewer: rd from sweden
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
Followup February 18, 2004 - 8pm Central time zone:
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 !!
February 19, 2004 - 7am Central time zone
Reviewer: rd from Sweden
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
Followup February 19, 2004 - 11am Central time zone:
utl_raw.cast_to_raw( dbms_lob.substr( clob data ) );
sure.
Multiple Mail recepients in the HTML mail
July 10, 2004 - 5am Central time zone
Reviewer: A reader
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
Followup July 10, 2004 - 9am Central time zone:
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
July 12, 2004 - 5am Central time zone
Reviewer: A reader
Hi TOM,
Thanks for the reply. I tried the method that you have specified in http://asktom.oracle.com/pls/ask/f?p=4950:8:18134091167212544557::NO::F4950_P8_DISPLAYID,F4950_P8_CR
ITERIA:255615160805 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 => '<B>TEST</B>' );
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 => '<B>TEST</B>' );
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 ?
Followup July 12, 2004 - 11am Central time zone:
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...
July 13, 2004 - 3am Central time zone
Reviewer: A reader
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
Followup July 13, 2004 - 11am Central time zone:
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.
July 14, 2004 - 11am Central time zone
Reviewer: A reader
Thanks TOM :)
Things are working perfectly now...
August 13, 2004 - 7pm Central time zone
Reviewer: A reader
Tom,
html_email works dandy, can you also show how to send gif/jpg through smtp.
Thanks.
Followup August 14, 2004 - 1pm Central time zone:
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
August 20, 2004 - 3pm Central time zone
Reviewer: Robert Pang from Redwood Shores, CA USA
Check out our UTL_SMTP code examples on OTN at http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/Utl_Smtp_Sample.html 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
November 15, 2004 - 2pm Central time zone
Reviewer: Randy
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
December 2, 2004 - 12pm Central time zone
Reviewer: chencho from Spain
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
Followup December 2, 2004 - 12pm Central time zone:
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 http://thatserver/img/logwin.gif
Friendly Sender
December 7, 2004 - 1pm Central time zone
Reviewer: jd
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
Followup December 7, 2004 - 8pm Central time zone:
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
December 9, 2004 - 6am Central time zone
Reviewer: Chencho from Spain
Thank you for your kindly cooperation. It works fine now.
January 18, 2005 - 8am Central time zone
Reviewer: Bevc from Germany
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 !
Followup January 18, 2005 - 10am Central time zone:
SQL> set define off
Random Exclamations
February 8, 2005 - 7pm Central time zone
Reviewer: Tim from Chicago, IL
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 ?
April 21, 2005 - 6am Central time zone
Reviewer: Rachit Khanna from India,Mumbai
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
Followup April 21, 2005 - 6am Central time zone:
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 ?
April 21, 2005 - 7am Central time zone
Reviewer: Rachit Khanna from India,Mumbai
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.
Followup April 21, 2005 - 11am Central time zone:
example and error stack -- WHERE is it happening. multi-byte data?
May 16, 2005 - 8am Central time zone
Reviewer: aparna from India
DBMS_LOB.WRITE( io_viol_text_array(I),2,1,TempHold1);
its gives the error that"No data found"
Followup May 16, 2005 - 12pm Central time zone:
that would mean that
io_viol_text_array(i)
has no value then. You are subscripting an array element you never set.
hi
December 4, 2005 - 3pm Central time zone
Reviewer: chinedu from egypt
hi pal what zup
Followup December 4, 2005 - 3pm Central time zone:
I think you mean "Whassup"
http://whassup.com/
whassup
December 6, 2005 - 4am Central time zone
Reviewer: A Reader from India
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
August 14, 2006 - 1pm Central time zone
Reviewer: bipin from UK
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.
Followup August 14, 2006 - 1pm Central time zone:
whose code is failing, what/which link?
I think I recognize this code
August 14, 2006 - 2pm Central time zone
Reviewer: John Flack from Arlington, VA
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.
Followup August 14, 2006 - 3pm Central time zone:
thanks - I don't monitor all bits of code out there in the ether :)
August 15, 2006 - 10am Central time zone
Reviewer: bipin from UK
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
September 11, 2006 - 5pm Central time zone
Reviewer: Ram from Austin
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
Followup September 11, 2006 - 6pm Central time zone:
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
November 9, 2006 - 7pm Central time zone
Reviewer: gregsheu from Federal Way, WA
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.
February 26, 2007 - 2am Central time zone
Reviewer: Ashish from India
Hi Tom
Is there any way to set the priority of an email sent through UTL_SMTP.
Followup February 26, 2007 - 2pm Central time zone:
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.
February 27, 2007 - 4am Central time zone
Reviewer: Ashish Rai from India
Thanks a lot, my problem is solved now.
Missing LF at end of MIME footer/boundary?
May 4, 2007 - 4pm Central time zone
Reviewer: Wayne from FL
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
October 15, 2007 - 12pm Central time zone
Reviewer: bc from macomb twp, mi
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 ?
Followup October 15, 2007 - 3pm Central time zone:
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
October 16, 2007 - 12am Central time zone
Reviewer: bc from macomb twp, mi
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
December 30, 2007 - 6am Central time zone
Reviewer: SHAHZAD from PAKISTAN
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
January 17, 2008 - 9am Central time zone
Reviewer: Davut Uysal from Turkiye
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%
April 4, 2008 - 5am Central time zone
Reviewer: Farrukh from Pakistan
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!
Followup April 4, 2008 - 10am Central time zone:
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
April 7, 2008 - 10am Central time zone
Reviewer: Venkateswaran AS from India
What is the meanin of empty_clob in
cBodyHTML CLOB := empty_clob;
Just curious to know.
Followup April 8, 2008 - 11am Central time zone:
it was not necessary.
Is it secure to send Email from Oracle Database
April 17, 2008 - 2am Central time zone
Reviewer: Muhammad Waseem Haroon (ocp_waseem) from Dubai, U.A.E.
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
Followup April 17, 2008 - 7am Central time zone:
... 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
May 1, 2008 - 6pm Central time zone
Reviewer: DEV from india
thank u tom u r a genious n if ever get n opportunity id love to meet u to have some more guidance
Followup May 1, 2008 - 9pm Central time zone:
That won't happen, you need a new keyboard apparently - before we could even think about communicating.
smtp error
May 2, 2008 - 3am Central time zone
Reviewer: ved from delhi/ india
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
Followup May 2, 2008 - 7am Central time zone:
... 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
May 2, 2008 - 9am Central time zone
Reviewer: ved from delhi/ india
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
Followup May 2, 2008 - 10am Central time zone:
"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
May 2, 2008 - 9am Central time zone
Reviewer: A reader
Has Oracle incorporated this type of code...
June 6, 2008 - 2pm Central time zone
Reviewer: Eric from St. Louis, MO USA
in the supplied packages since this was originally written?
Followup June 9, 2008 - 10am Central time zone:
what type of code?!?!?!?
Thanks for the question regarding "Sending HTML using UTL_SMTP"
June 12, 2008 - 10am Central time zone
Reviewer: Chris from Halifax, NS, Canada
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.
Followup June 12, 2008 - 11am Central time zone:
...
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"
June 12, 2008 - 12pm Central time zone
Reviewer: Chris from Halifax, NS, Canada
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
June 18, 2008 - 1am Central time zone
Reviewer: Nana from Malaysia
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!
Followup June 18, 2008 - 12pm Central time zone:
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
February 28, 2009 - 2pm Central time zone
Reviewer: Mohammad Montaser from egypt
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
Followup March 3, 2009 - 10am Central time zone:
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
April 14, 2009 - 11am Central time zone
Reviewer: prashant from Ind
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..???
Followup April 14, 2009 - 12pm Central time zone:
"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.
April 14, 2009 - 1pm Central time zone
Reviewer: prashant from Ind
Hi Tom,
Thank's now its working..... Earlier i didn't provided that html parameters.....
April 28, 2009 - 11pm Central time zone
Reviewer: stephen from India
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
Followup April 29, 2009 - 8am Central time zone:
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
April 30, 2009 - 2am Central time zone
Reviewer: Stephen from India
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.
Followup April 30, 2009 - 10am Central time zone:
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
May 1, 2009 - 8am Central time zone
Reviewer: stephen from India
Hi, I was wondering , why it is not coming as an attachment.
Followup May 1, 2009 - 10am Central time zone:
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
May 12, 2009 - 2am Central time zone
Reviewer: A reader
can we use a sql statement in utl_smtp and send the result with it ?
Followup May 13, 2009 - 10am Central time zone:
describe to us what is means to "use a sql statement in utl_smtp" please?
I don't understand what you mean by that.
May 18, 2009 - 12pm Central time zone
Reviewer: Ahmad Zahabi from Tehran IRAN
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
Sending e-mail
July 29, 2009 - 6am Central time zone
Reviewer: Hamed Al Najjar from ksa
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...')
Followup July 29, 2009 - 7am Central time zone:
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
September 2, 2009 - 1pm Central time zone
Reviewer: A reader
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
Followup September 2, 2009 - 2pm Central time zone:
$ 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
September 24, 2009 - 4pm Central time zone
Reviewer: A reader
thanks is very useful.
Email HTML
October 10, 2009 - 6pm Central time zone
Reviewer: Francis from San Diego, CA USA
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
October 14, 2009 - 7am Central time zone
Reviewer: Rajkumar from UAE
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.
|