Email
Ashokkumar, November 13, 2001 - 11:52 am UTC
Tom
Vey Good Response
Can u suggest me a way to send xls file as an attachment using utl_smtp .(XLS Stored as a blob in the database)(Pure pl/sql no java)
November 13, 2001 - 12:53 pm UTC
Well, you are ALREADY using java!!! UTL_SMTP uses UTL_TCP and UTL_TCP in 8i is Java.
See
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1739411218448 <code>
it shows howto with a SIMPLE html attachment- a binary file would be more involved (you would have to write a base 64 encoder to encode the XLS file and so on)
Since ALL of this code works in Java out of the box -- i would suggest you use Java for doing this! Its really easy.
Problem with SMTP transaction
Nishant Manchanda., January 25, 2002 - 2:13 am UTC
Hi Tom,
I tried the procedure written by Mr.Parag.There is some problem relating to SMTP transaction at my end.It displays the error handled in the exception.Please tell me what has to be done for SMTP connection.
Thanking you,
regards,
Nishant Manchanda.
Good Insight
Rama Subramanian G, June 30, 2004 - 5:20 am UTC
Hi Tom
Your reply to the question provides a nice insight into the specifics of email through UTL_SMTP.
I know oracle supports SSL, but is there any way of handling digitally signed email messages ? I mean retrieving digitally signed email messages, parsing the signature to check its validity, acceptablilty etc. ?
I shall really appreciate if you could provide with a lead.
June 30, 2004 - 10:10 am UTC
we do not have any built in packages to RECIEVE mail.
You would load up a java class (I've used the java mail api for example in the past). If you can find the java classes to do that, sure.
Send email with attachment
Sean, August 27, 2004 - 5:44 pm UTC
Hi Tom,
I have a task to use job to create statspack report once a day and send email with this report as an attachment. I am trying to use Oracle product to finish this project without any shell script.
Here is the questions about email. I have copied this stored procedure from chapter 18 of Developer's guide(920). It works fine. But how can I send an email with the attachment?
-------------------------------------
create or replace PROCEDURE send_test_message
IS
mailhost VARCHAR2(64) := '10.200.10.107';
sender VARCHAR2(64) := 'sli@company_name.com';
recipient VARCHAR2(64) := 'sli@company_name.com';
mail_conn utl_smtp.connection;
BEGIN
mail_conn := utl_smtp.open_connection(mailhost, 25);
utl_smtp.helo(mail_conn, mailhost);
utl_smtp.mail(mail_conn, sender);
utl_smtp.rcpt(mail_conn, recipient);
-- If we had the message in a single string, we could collapse
-- open_data(), write_data(), and close_data() into a single call to data().
utl_smtp.open_data(mail_conn);
utl_smtp.write_data(mail_conn, 'This is a test message.' || chr(13));
utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13));
utl_smtp.close_data(mail_conn);
utl_smtp.quit(mail_conn);
END;
/
-------------------------------------
Thanks so much for your help.
Mark, May 06, 2005 - 10:38 am UTC
Hi Tom,
I am having an intermittent problem where a Java procedure, which sends mail with an attachment, seems to never complete. (Unfortunately it hasn't popped up since I added copious instrumentation.) Querying V$ACCESS, I can see that locks on this procedure are held and never go away. Once I made the mistake of recompiling this procedure while these locks were held. Not only did the procedure never compile, no one could even call the procedure at that point.
Oracle support told me the following, which seems odd to me:
----------
CAUSE DETERMINATION
====================
The sending of mail from a custome Java SP shold be discouraged as Oracle in 10g provides two methods.
UTL_SMTP and DBMS_MAIL
UTL_SMTP Is generally a wrapper to javax.mail, much the same as the code provide
d. Its use is discoraged as it it difficult to use and problematic. UTL_SMT
P's lack of robustness spawned the creation of the 10g Package DBMS_MAIL. Where
the mail is created and queued to be sent. If the fisrt attemp fials it will t
ry until it is sent. Mush like a standard mail client.
CAUSE JUSTIFICATION
====================
Even Tom's book noted the UTIL_SMTP cld not be relied upon to send large mail and atachements. If the Oracle use of
javx.mail , as in UTL_SMTP cannot be used then custom code could nto be any bett
er.
POTENTIAL SOLUTION(S)
======================
Use DBMS_MAIL It is designed more like a standard mail client.
(a couple of dead links provided here)
---------
I spoke to this guy on the phone prior to his writing this info on Metalink. I mentioned that your book and/or site suggested using javamail over utl_smtp, particularly if you need to send attachments (which I am doing). He seems to have used this info in his "cause justification", which I don't quite understand.
If I search for dbms_mail on OTN, I find no results under 10g - only for 9i and 8i - and in those docs the package is just briefly mentioned. I'm particularly confused because I was told this package was new to 10g. If I google for it, I find this: </code>
http://www.unix.org.ua/orelly/oracle/prog2/appc_08.htm <code>. Apparently I would need to install Oracle Office.
I'd like a second opinion, if you could be so kind. I don't expect you to know why my Java call is hanging, but do you think support has me barking up the wrong tree here? Have you ever had javamail hang indefinitely? Do you have any links to good info about dbms_mail?
Thanks a lot,
Mark
May 06, 2005 - 11:44 am UTC
dbms_mail would not make sense. My book made no such "note" -- but rather that in 8i the sending of attachments would be hard since you would have to encode them and the encoding routines didn't exist.
I believe the analyst meant UTL_MAIL, not dbms_mail
A reader
A, June 17, 2005 - 6:00 am UTC
Hi,
I want to have a procedure which should do following..
1)select count(*) based on some calulation..
2)select count(*) based on another calculation..
if any of that count(*) is greater than zero then send an email stating that record has come.
I will be scheduling this procedure(every half an hour) to check using dbms_job.
Any example for above will be appreciated.
Cheers
June 17, 2005 - 2:57 pm UTC
how about:
select count(*)
into l_cnt
from t
where ( <condition1> or <condition1> )
and rownum = 1;
if (l_cnt > 0)
then
send your mail
end if;
Good
Saurabh, September 08, 2005 - 3:18 am UTC
Very Useful
Dave Thompson, October 28, 2005 - 6:48 am UTC
Saurabh,
That is an excellent link.
Thanks.
Unable to send email from oracle !
william, December 08, 2005 - 9:07 am UTC
i already implemented your sample code for sending email using Oracle Store Procedure. here is the code :
CREATE OR REPLACE package body PGV_MAIL as
g_crlf char(2) default chr(13)||chr(10);
g_mail_conn utl_smtp.connection;
g_mailhost varchar2(255) := '192.168.207.12';--'mail.beacukai.go.id';
-- username : aryoni@beacukai.go.id password : 060101728
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,
p_to in array default array(),
p_cc in array default array(),
p_bcc in array default array(),
p_subject in varchar2,
p_body in long)
is
l_to_list long;
l_cc_list long;
l_bcc_list long;
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_sender_email);
l_to_list := address_email( 'To: ', p_to );
l_cc_list := address_email( 'Cc: ', p_cc );
l_bcc_list := address_email( 'Bcc: ', p_bcc );
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 );
utl_smtp.write_data( g_mail_conn, '' || g_crlf );
utl_smtp.write_data(g_mail_conn, p_body );
utl_smtp.close_data(g_mail_conn );
utl_smtp.quit(g_mail_conn);
end;
end;
then i executed the procedure like this :
begin
pgv_mail.send
( p_sender_email => 'someone@government.go.id',
p_from => 'Oracle Database Account <someone@government.go.id>',
p_to => pgv_mail.array( 'person1@government.go.id','person2@government.go.id' ),
p_cc => pgv_mail.array( 'person3@government.go.id' ),
p_bcc => pgv_mail.array( 'person4@government.go.id' ),
p_subject => 'This is a subject',
p_body => 'Hello Tom, this is the mail you need'
);
end;
but it failed when executed with error message : SMTP permanent error: 554 Mail from someone@government.go.id rejected for policy reasons.
there is nothing error with SMPT server, because it just succeded when i send email using webmail application and email account someone@government.go.id.
Why it failed to send email usign Oracle Procedure but succeded using webmail application ?
thx in advanced
javamail needs update
bc, December 09, 2020 - 3:48 am UTC
I have reached the point where I need to update the JavaMail library to at least 1.6, As TLS 1.2 is supported by Java Mail 1.6 and higher.
The Java Mail jar file shipped with our databases are version 1.4 or lower.
How do I update the mail.jar or javax.mail.jar file in my database home ?
Thanks
BC
December 11, 2020 - 2:51 am UTC
I would ditch it and use APEX_MAIL instead.
Transactional, easier volume control, set resource limits, etc etc etc...