Sunday, June 27, 2010

Exhausted ResultSet

Hi, if you ever encounter this kind of error

java.sql.SQLException: Exhausted Resultset
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:269)
at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:3195)
at oracle.jdbc.driver.OracleStatement.getIntValue(OracleStatement.java:4264)
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:510)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:1528)



you might guessed that this error happen when you trying to access a resultset which already being closed or never been advanced to the first record, but you very certain that the statement is never been closed or always advanced to the first record and still these kind of error spitting out in your log or console, you might wanna check if the same statement object had been re-executed. In my case another thread executing the same statement object, thus the previous resultset is reset.
  1. thread A is executing the statement and get the resultset X
  2. thread A advance the resultset X by calling X.next()
  3. thread B is executing the statement and get the resultset Y
  4. thread A try to call X.getString("some_field") => spit the error
  5. thread B advance the resultset Y by calling Y.next()
  6. thread B try to call Y.getString("some_field") => working fine

took me whole night figure this one out, should have read the spec earlier, stupid me...

4 comments:

mahesh krishna said...

Great help thank you, I was stuck with same problem.

Unknown said...
This comment has been removed by the author.
Unknown said...

Thankx Man.....!!
But my problem is still there .I am using only one resultset and i do check next() and with out next() both........!!
Plz sort this out.....!!

Anonymous said...

Same issue am facing.. But I couldn't understand how you resolved the issue and what is the problem in multi threaded enviorment.. Can you detail it?