RE: Intermittent 421 errors using UTL_SMTP

Howdy,

In the interest of completeness, I thought I'd post my solution. As mentioned, I'm using a slightly improved version of maildemo.sql. My problem with this plagarism is not understanding the timing of the SMTP calls and I had inadvertently opened the SMTP connection to Exchange before starting my BULK COLLECT. So, when the DB got busy, my queries ran longer, causing Exchange to timeout, which apparently results in a 421 error.

The simple fix is to move the OPEN_CONNECTION call to be after the BULK COLLECT instead of before.

Enjoy!
Rich

• From: "Rich Jesse" <rjoralist_at_xxxxxxxxxxxxxxxxxxxxx>
• To: oracle-l_at_xxxxxxxxxxxxx
• Date: Mon, 22 Oct 2007 12:52:29 -0500 (CDT)

Hey all,

I have a few PL/SQL procedures in 10.1.0.5.0 on AIX 5.3 that indirectly use UTL_SMTP to send out email (duh) through our Exchange server. The package that my procedures use to call UTL_SMTP is a slightly improved version of
"maildemo.sql" (Google it).

While this works fine most of the time, I have one weekly DBMS_SCHEDULER job that now consistently fails, while the other ten jobs work flawlessly. However, when I manually run the weekly job after a fail, it usually works (this morning I needed to run it twice). Since only this job fails, all other jobs work, and all jobs use the same entry point to UTL_SMTP, I believe that the mail server and related variables are correctly set. Here's the important part of the error stack:

ORA-29278: SMTP transient error: ORA-29278: SMTP transient error: 421 Service not available

ORA-06512: at "SYS.UTL_SMTP", line 21
ORA-06512: at "SYS.UTL_SMTP", line 97
ORA-06512: at "SYS.UTL_SMTP", line 342
ORA-06512: at "RICH.MAILDEMO", line 332

UTL_SMTP is wrapped, so I can't say what those lines are, but the line my package fails on (called "RICH.MAILDEMO" here) is calling
"UTL_SMTP.CLOSE_DATA".
This seems to happen when the instance has a lot of activity, but IMHO nowhere near peak. As I don't have visibility to the Exchange server performance, I can't speak to that. Also, I see that the parameter
"tx_timeout" in the call to UTL_SMTP.OPEN_CONNECTION is not present, which
should default to a NULL, or "wait indefinitely", according to the docs. There is also mention in the package comments that this parameter may not affect writes as documented, but it doesn't say what the implemented handling is. Finally, since this is from a DBMS_SCHEDULER job, I don't believe it would qualify for BUG 4083461.

Anyone have some ideas on how to troubleshoot this? While it's not a priority, the intent of the job is to automate the report, which it's now not doing...

TIA!
Rich

--
http://www.freelists.org/webpage/oracle-l