UTL_SMTP hangs while sending

We are using Oracle 8.1.7.4.2.

For some customers, we implemented an E-Mail-Messaging and Newsletter-System for sending large amounts of personalized mails. The System bases on utl_smtp. For performance-issues, it is sending in parallel using dmbs_job (a number of single jobs are submitted, each single job consists of a single procedure call for sending just one single mail).

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.

Killing the job and the assigned session(s) does not help; the locks in v$lock (Type="JQ") will resist even if the session got's in killed or disconnected-state.

Because we are using 8i, the maximum number of parallel jobs is limited to 32, so if the discribed situation occures for 32 times, no more jobs from the jobqueue will be processed.

additionally, oracle does not throw any kind of exception or error-message.

The only workaround we figured out is to restart the database ... which not parctical in an production-environment.

In 9i, the procedure utl_smtp.open_connection got a parameter "timeout" ... but upgrading to 9i is currently not an option.

Any ideas?

Best Regards

Marc Received on Mon Aug 04 2003 - 09:52:13 CDT