UTL_MAIL or UTL_SMTP [message #597926] |
Wed, 09 October 2013 05:37 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
Hi -
I developed a program to send e-mails from Oracle using both UTL_MAIL as well as UTL_SMTP. My requirement now is to send around 75,000 emails per day, When I tested for 1000 emails it is taking around 6 mins., Then for 75,000 it would be around 7-8 hours.
I really now not understanding what would be the best way to achive this. Not to put the server busy for these many hours.
Can we really send this much of e-mails from Oracle?
Pls. help what would be best approach to do this.
(UTL_MAIL is little faster than UTL_SMTP)
Siva
|
|
|
|
Re: UTL_MAIL or UTL_SMTP [message #597988 is a reply to message #597937] |
Wed, 09 October 2013 10:56 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
Thank you - Tested in UNIX box - again taking the same time for 1000 e-mails.
One more question - In Oracle in the TO list with multiple recipients, if one e-mail is wrong - it throws and error and no one gets e-mails, Where as I tested in UNIX it works fine - Good ids gets emails, Bad fails. Any options to make this work in Oracle. And Multiple recipients only works in UTL_MAIL.
|
|
|
|
Re: UTL_MAIL or UTL_SMTP [message #598213 is a reply to message #597926] |
Fri, 11 October 2013 08:58 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
Thank you Michel.
I tried mail_pkg and It worked. (I choosen this because in my requirement, mails has to be sent to only BCCs, Not To's)
But still I see if any one mail is not good - it throws an error and ignores others sending.
Was looking into the mail validation program - is this good to do?
Before making a list of all good e-mails - I want to check if my SMTP server can validate the good and bad e-mails. Then I can send only good ones into my email procedure.
Can you confirm if I can use this - I tested for few cases with my organization emails.
Good ones it says
CODE = 251 TEST = 2.5.0 Local alias matched by <abc@bcd.com>
Bad ones it says
CODE = 550 TEST = 5.1.1 String does not match anything.
DECLARE
l_mailhost VARCHAR2(255) := 'xx.xxx.xxx.xxx'; -- smtp server
l_mail_conn utl_smtp.connection;
l_reply utl_smtp.reply;
BEGIN
l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.helo(l_mail_conn, l_mailhost);
l_reply := utl_smtp.vrfy( l_mail_conn, 'abc@bcd.com' );
dbms_output.put_line( 'CODE = ' || l_reply.code || ' TEST = ' || l_reply.text);
utl_smtp.quit(l_mail_conn);
end;
|
|
|
|
|
Re: UTL_MAIL or UTL_SMTP [message #598354 is a reply to message #598222] |
Mon, 14 October 2013 04:31 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
Is there any other way in Oracle to verify e-mail ids before sending mails in batches. (I am doing this just for my office e-mails)
Since there are 75K mails I have to send on daily jobs - I designed mail_pkg program to sends 1000s in batches.
So I want to verify before sending 1000s in batches.
Is there any limit of sending e-mails through UTL_SMTP.
I tested for 1000 emails sending in BCC - it worked. Wanted to know if any limit restrictions.
|
|
|
|
|
Re: UTL_MAIL or UTL_SMTP [message #598495 is a reply to message #598369] |
Tue, 15 October 2013 05:52 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
Thanks Michel - I will see if my DBA helps on this.
I would appreciate if any one can help me on the below questions -
Is there any other way in Oracle to verify e-mail ids before sending mails in batches. (I am doing this just for my office e-mails)
Since there are 75K mails I have to send on daily jobs - I designed mail_pkg program to sends 1000s in batches.
So I want to verify before sending 1000s in batches.
Is there any limit of sending e-mails through UTL_SMTP.
I tested for 1000 emails sending in BCC - it worked. Wanted to know if any limit restrictions.
|
|
|
|
Re: UTL_MAIL or UTL_SMTP [message #598499 is a reply to message #598498] |
Tue, 15 October 2013 06:34 |
sivakumar121
Messages: 29 Registered: April 2009
|
Junior Member |
|
|
It says -
recipients buffer - The maximum total number of recipients that must be buffered is 100 recipients.
But when I tested for 1000 e-mails - It still did not throw any errors - directly I did not send to 1000 different e-mail ids - I tested with a single id using 1000 commas and sent it - I can see all the ids in the TO list in the output.
Confused now to use just 100 or 1000 works.
|
|
|