utl_smtp Hanging when opening a connection

From: <collins.paulj_at_googlemail.com>
Date: Thu, 29 Nov 2007 06:52:45 -0800 (PST)
Message-ID: <e80fa935-4d3a-4da6-99b1-20836510af35@a35g2000prf.googlegroups.com>

We are using utl_smtp to send emails from our database. We currently run Oracle 9.2 on RHEL4, with Postfix as our mailserver.

Lately, we have noticed that the process creating the email is hanging, which often requires the database to be taken down to fully kill off the session. (This is on our test system, fortunately).

Having looked at a numnber of posts on various forums, I have seen that this is not an uncommon problem. Oracle have included a timeout parameter in utl_smtp.open_connection, but this is not implemented for write processes (in version 9.1 to 10.2, although more may be the same), and from comments, I have seen that this timeout functionality does not apply to opening the connection itself.

Has anybody come up with a solution to programmatically cause the open connection to timeout if a connection is not established in a reasonable time, and if so, can you please help me.

Many thank in anticipation,

Paul Received on Thu Nov 29 2007 - 08:52:45 CST