Error Handling with SMTP Server

User_OR6IU

Hi,

I use the UTL Package for sending emails via Oracle Database Function ( Database Version 12g), sometimes

i get this error:

ORA-29278 Transient Error: 451 4.7.0 Timeout waiting for client input

question is here, how can I handle this error, I thought that I use a try catch and implement

a wait/Sleep, try catch is implemented but what is the best Practice for sleep timer ?

User_OR6IU

ORA-04021

is also comes up

any ideas ?

Mike Kutz

No idea.

Please post simplified code and full error stack.

Billy Verreynne

Error 4yz is documented in rfc5321

   4yz  Transient Negative Completion reply
      The command was not accepted, and the requested action did not
      occur.  However, the error condition is temporary, and the action
      may be requested again.  The sender should return to the beginning
      of the command sequence (if any).  It is difficult to assign a
      meaning to "transient" when two different sites (receiver- and
      sender-SMTP agents) must agree on the interpretation.  Each reply
      in this category might have a different time value, but the SMTP
      client SHOULD try again.  A rule of thumb to determine whether a
      reply fits into the 4yz or the 5yz category (see below) is that
      replies are 4yz if they can be successful if repeated without any
      change in command form or in properties of the sender or receiver
      (that is, the command is repeated identically and the receiver
      does not put up a new implementation).

The detailed error message you posted indicates the server is timing your code out.

Thus either your SMTP transaction is incorrectly handled in your code, or you are running into a network service issue of sorts.

Mike Kutz

Thus either your SMTP transaction is incorrectly handled in your code, or you are running into a network service issue of sorts.

The ORA-04021 suggests that the code is performing DML/Select operations that can take a long time.

I really hope the OP is sending email based on commited data.

Billy Verreynne

Mike yes - but the client needs to be free from user processing (such as DML) in order to receive the exception from the SMTP server. Unless this is send by the SMTP server during the user processing and encountered afterwards on a UTL_SMTP code call made by the user code. But then I would expect that the SMTP server ceased waiting and closed its socket handle already.

I often run into the reverse though talking smtp with MS Exchange. UTL_SMTP timing it out (after 5 mins) as it is not responding to the data end call (period send) to complete the mail transaction.

Mike Kutz

Billy,

I was think of a process like this:

1. connect to SMTP server ( maybe say HELO )
2. long PL/SQL process
3. continue SMTP conversation

where as:

• step 2 could occasionally throw ORA-04021
• step 3 receives the 451 4.7.0 from the SMTP server

But, a missing . could also do it. A bad conversation might also be involved.

BUT

Without reviewing the code, we're probably just throwing darts while blind-folded.