Oracle Resultset slow

[Peter]

I have a problem with an Oracle Resultset using jdbc. It works about once a week when doing a simple
long value = results.getLong("value");
the process will hang and start spinning and use up about 75% of the CPU on the server. The only way to get it going again is to kill it and restart. As you can guess the other users aren't very happy when this happens.

what's the problem???
thanks

Code:

for(;;) {
Statement mainStmt = connection.createStatement();
java.sql.ResultSet results = getData(mainStmt);
if (results != null) {
String message = null;
try {
while (results.next()) {
message = null;
//this is the statement that hangs.
mod_date = results.getLong("modified_date");
try {
message = createXMLString(results);
publishMessage(message);
} catch(Exception ex) {
errorHandler(false, 0, procName,
"Unable to create TextMessage for "
+ results.getString("ID") + ":" + ex.toString());
message = null;
publishError = true;
}
} //while

results.close();
results = null;
mainStmt.close();

} catch (Exception ex) {
errorHandler(false, 0, procName, "unknown error:"
+ ex.toString());
ex.printStackTrace();
}
}
rest(60);
} //for

[Heather]

When you are creating following object
Statement mainStmt = connection.createStatement();
java.sql.ResultSet results = getData(mainStmt);
in for loop will make the heap occupied with the objects. instead, place the same statements out side the for loop and also String message = null as well. Objects which are created in the loop, should be moved outside the loop, would solve your problem.

[Ed]

Database object may not be closed properly, which is causing the pile up of objects in the heap