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

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Chris Saxon

Thanks for the question, Arun.

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

Answered by: Chris Saxon - Last updated: January 14, 2020 - 5:19 pm UTC

Category: PL/SQL - Version: Oracle DBCS

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Parsing Densities, ANYDATA, and XML.

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 we 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.

More to Explore

PL/SQL

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