Hi,
I am using jboss-3.2.3 and oracle 9i. I have broken pipe error when the sql statement takes long time (more than 15 minutes) to execute. This problem will not happen if the sql execution is fast. The error log is as follows:


2005-03-06 18:57:58,959 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Exception destroying ManagedConnection
org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (java.sql.SQLException: Io exception: Broken pipe)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.checkException(BaseWrapperManagedConnection.java:546)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.destroy(BaseWrapperManagedConnection.java:252)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.doDestroy(InternalManagedConnectionPool.java:502)
at org.jboss.resource.connectionmanager.InternalManagedConnectionPool.returnConnection(InternalManagedConnectionPool.java:326)

Caused by: java.sql.SQLException: Io exception: Broken pipe
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:334)
at oracle.jdbc.driver.OracleConnection.close(OracleConnection.java:1478)
at org.jboss.resource.adapter.jdbc.BaseWrapperManagedConnection.destroy(BaseWrapperManagedConnection.java:248)


I've suspect 2 problems which cause this error

1) For some reason the connection between the AppServer and the database is lost. May be that the DBMS itself or a firewall may shut down sockets/connections that are idle for too long.

2) The jboss connection to the oracle server had timeout.

In my oracle-ds.xml, my setting is as below:


<local-tx-datasource>
<jndi-name>DB</jndi-name>
<connection-url>jdbc:oracle:thin:@10.10.10.10:1521:ora</connection-url>

<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>user</user-name>
password
<min-pool-size>10</min-pool-size>
<max-pool-size>300</max-pool-size>
<blocking-timeout-millis>10000</blocking-timeout-millis>
<idle-timeout-minutes>20</idle-timeout-minutes>
<new-connection-sql>select sysdate from dual</new-connection-sql>
<check-valid-connection-sql>select sysdate from dual</check-valid-connection-sql>
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
</local-tx-datasource>



Even though i change the idle-timeout from 20 minutes to 40 minutes, the i still get this error. Do i need to set <set-tx-query-timeout> true </set-tx-query-timeout>? What is the <set-tx-query-timeout> for?

Pls help. Thanks in advance