Re: UTL_SMTP hangs while sending

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0308041101.4240b54b_at_posting.google.com>...
> md_at_burning.de (Marc) wrote
>
> > We are using Oracle 8.1.7.4.2.
> <snipped>
> > we got the following problem:
> > It may happen, that the vpn-connection between database server and
> > mail-server wents down for a second, while a job is sending (using
> > utl_smtp.write_text). If this happens, the job "hangs" - control is
> > not given back.
> <snipped>
>
> Seeing that we do not have the UTL_SMTP source... Marc, this sounds
> like a blocking call problem.. which is likely how UTL_SMTP is
> implemented?
>
> And yes, Oracle will not raise an exception as there is nothing to
> raise. The IP stack has the execution pointer and it is stuck. While
> Oracle waits for blocking call to complete.
>
> I hate blocking calls in sockets programming. It is a pain as you
> release control of a call to the IP stack. For example, you do a
> send() and send the RCPT TO data. Next you do a recv() to get the ack
> from the SMTP server. Only, something goes wrong and there is nothing
> to recv().. ever.. until something gets killed or kicked.
>
> UTL_TCP itself can be used without having to run into such a blocking
> call problem (e.g. you check if there's data in the recv() buffer
> before doing a recv() and reading the exact number of bytes in the
> buffer).
>
> Anyway IMO, if UTL_SMTP has been implemented using "default blocking"
> calls, there is _nothing_ you can do to fix it... but to rewrite it.

Billy, your explanation sounds quite reasonable. The only thing I want to say is that when you say IP stack, you really sound like saying TCP stack.

To Marc, how about setting tcp_keepalive_interval (or tcp_keepalive_time depending on OS) to a lower value? Most OSes set it to 2 hours (I think it's some standard body recommended minimum). Tru64 has it at 4 hours; Linux maybe 3 hours. When your problem happens, can you not bounce the database and wait for your OS-specific tcp keep alive time to see if you get a timeout? Unfortunately, even if this can reduce the timeout, it's a system-wide setting.

Yong Huang Received on Mon Aug 04 2003 - 23:23:44 CDT