I also saw
java.sql.SQLException: Io exception: Broken pipe
or sometimes
java.sql.SQLException: No more data to read from socket
I did manage to make the problem go away, but my fix is not very clean and efficient.
I'm working with an old JDBC version which doesn't provide 'DataSource' connection pooling, so I'm having to use DBConnectionBroker from javaexchange.com to connect to Oracle
This worked fine most of the time, but then DbConnectionBroker started returning dead connections, which cause the above errors when I run queries.
The trick is to detect dead connections before DBConnectionBroker returns them for use. It already has a 'housekeeping
thread', which should find these dead connections, and reestablish them. It runs the checks every 20 seconds. Before it called connection.createStatement to see if the connection was still alive, but I found with oracle JDBC this
test was not enough. It was allowing createStatement on a connection object which was dead (and destined to cause an error later)
I changed it so that it actually fires a query, to be absolutely sure the connection is alive:
stmt = connPool[i].createStatement();
ResultSet rset = stmt.executeQuery("SELECT SYSDATE FROM DUAL");
Although this must be a pretty fast query for the oracle server to cope with, it also an unnecessary query, which it is firing it every 20 seconds. So this seems like a bit of an ugly inefficient solution. Anyone have any better ideas for detected dead connection objects?