Skip to Main Content
  • Questions
  • UTL_SMTP not sending email consistently

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: January 14, 2020 - 5:31 am UTC

Last updated: May 26, 2021 - 1:06 pm UTC

Version: Oracle DBCS

Viewed 1000+ times

You Asked

Hello Experts.

I am facing some issues with sending email from DBCS using UTL_SMTP

My package is as below:

----------

create or replace PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);
  UTL_SMTP.data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.quit(l_mail_conn);
END;


----------

Procedure call:

BEGIN
    ncr_send_mail('arun.pasupathy@aaa.com', 
                  'noreply@pprcuat.localdomain', 
                  'Test Message from SMTP - UAT 4', 
                  'localhost');
END;


-------

SMTP and ACL is configured on the Database.

When I call this procedure, I get the email, but it is inconsistent.
If I make the same procedure call 10 times, for example, I get email 5-6 times.
The remaining 4 times I don't get any email.

This is causing issues for me in many UAT code packages as emails are not sent consistently.

Can you please advise what I need to check.
Doesn't seem to be a code issue to me as I do get emails a few times.

Thanks in advance for your time.

Thanks,
Arun

and Chris said...

It's possible that there's an issue with the network and/or the email server. Or the mail server may have rules in place to throttle traffic if it gets lots of messages with the same from/to addresses.

So work with your network and email server admins to see if the requests are making it to the mail server.

If the mails aren't making it to the mail server, add logging to your code to verify all the steps are working.

Rating

  (2 ratings)

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

Comments

Satish, May 25, 2021 - 10:56 pm UTC

Yes. This is happening for us too. The emails are sent intermittently when we run the program/batch for 50+ or 100+ emails. We are also unable to capture any error on the oracle side. Were we able to find the root cause. Is there any alternate option.
Chris Saxon
May 26, 2021 - 1:06 pm UTC

Were we able to find the root cause

I assume you mean NOT able?

As stated above, you need to add logging to figure out exactly where the issue is

Security from Google to detect spam.

Chien Lin, August 10, 2022 - 8:58 pm UTC

We had this problem of intermittent email and discovered enhanced spam detection from google prevented some of our emails from going through.
Emails which did get through would have an alert at top of email stating potential spam,

after we set up SPF on our email server we were able to get the emails delivered without the alert.

Here is a link on email authentication.
https://support.google.com/a/answer/10583557

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library