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)

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.

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.

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.

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

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

Hi Tom,
I found the response very useful. There is a utility from sun to send emails with attachments without using oracle utl_smtp package. Follow this link for further details: </code> http://www.akadia.com/services/java_mail_plsql.html <code>

Saurabh,

That is an excellent link.

Thanks.

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

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