If you are executing the query in the context of a transaction, the transaction timeout value of the JTA transaction monitor will be the determinant to query timeout. The configuration for this depends from one application server to another.
At an individual query level (in the absence of a JTA transaction monitor), the setQueryTimeout method can be used to set the timeout on the execution of a Statement/PreparedStatement/CallableStatement object.
Update
setQueryTimeout is not to be relied on, although it works (atleast from a J2SE client). It works via the JDBC driver performing a full round-trip to the Oracle database server. Then, it is upto the database to halt execution of the query. Don't rely on it for time critical applications.
We are having problems with JTA timeout as it does not affect running query. And actually WebLogic server which we run on tries to kill the long running connection and creates another thread to kill it, but it hits Java lock in Oracle JDBC driver thus causing both threads to wait. The problem becomes even worse as WebLogic tries to create even more killer-threads and eventualy runs out of them.
The point about setQueryTimeout seems very intresting and I haven't thought of that before actuallly :). We don't really care if setQueryTimeout will take some time to cancel the query, the main thing here is the result :). I will get back on the test results.
The reason setQueryTimeout() might not work in WLS, is more so because of JTA. I don't think the driver will respond to setQueryTimeout calls in a transaction context, but I might be incorrect.
In case your transaction timeout value is too high, you could verify the timeouts set in the JTA service for WLS, and in trans-timeout-seconds property in ejb-jar-xml (for CMTs) and in UserTransaction.setTransactionTimeout() for BMTs.
It seems that setQueryTimout actually works in JTA context, but not always. Sometimes it times out the query after 5 minutes (the value I set in the method), sometimes after 20 minutes, sometimes after 60 minutes.