Forum Stats

  • 3,783,022 Users
  • 2,254,721 Discussions
  • 7,880,247 Comments

Discussions

Error Handling with SMTP Server

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 ?

Answers

  • User_OR6IU
    User_OR6IU Member Posts: 3 Green Ribbon

    ORA-04021

    is also comes up


    any ideas ?

  • Mike Kutz
    Mike Kutz Member Posts: 5,988 Silver Crown

    No idea.

    Please post simplified code and full error stack.

  • Billy Verreynne
    Billy Verreynne Software Engineer Member Posts: 28,655 Red Diamond

    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
    Mike Kutz Member Posts: 5,988 Silver Crown

    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
    Billy Verreynne Software Engineer Member Posts: 28,655 Red Diamond

    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
    Mike Kutz Member Posts: 5,988 Silver Crown

    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.