Message-ID: <42503.12.17.117.251.1197659737.squirrel@12.17.117.251>
Date: Fri, 14 Dec 2007 13:15:37 -0600 (CST)
Subject: RE: Intermittent 421 errors using UTL_SMTP
From: "Rich Jesse" <rjoralist@society.servebeer.com>
To: oracle-l@freelists.org
User-Agent: SquirrelMail/1.4.3a
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: rjoralist@society.servebeer.com

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@xxxxxxxxxxxxxxxxxxxxx>
    * To: oracle-l@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