I don't think there is a practical way to do so using an existing ResultSet. But you could run several queries, each of which would return a subset of the original query.
I have a result set object which contains 23000 records in it. Using prepared statements and batch insertion i am inserting this data into database. But i got a
' ora-01000 maximum open cursors exceeded ' exception.
I tried using Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) method to close cursors on commit...but i found out that Oracle Database only supports HOLD_CURSORS_OVER_COMMIT. Can you suggest me any alternative?
The ORA-01000 error generally means that you're leaking database resources. Splitting the ResultSet will result in more cursors to be opened and, if anything, will make things worse.
I don't think ResultSet.CLOSE_CURSORS_AT_COMMIT is going to help you either. You generally don't need to commit frequently in Oracle, and certainly you should not commit while reading from a ResultSet. But even if you do, you risk the ORA-01555 error, not ORA-01000.
It looks like you're not properly closing statements or resultsets in your code. Perhaps we could help more if you post your full code.
Shankara Sharma wrote:I have a result set object which contains 23000 records in it. Using prepared statements and batch insertion i am inserting this data into database.
Are you fetching data (23000 records!) out of the database then trying to write the same data back to a different table? If so, why not leave the data in the database and do it all in a single SQL INSERT instead?
@ Martin Vajsar : That's not the case because... the same business logic is working in SQL Server DB. SQL server supports Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) parameter.
While(resultSet.next())
{
Batch insertion for every 1000 records...
}
}
Catch()
{
Exception handling
}
finally()
{
Close result set.
Close prepared statement.
}
Shankara Sharma wrote:@ Martin Vajsar : That's not the case because... the same business logic is working in SQL Server DB. SQL server supports Connection.setHoldability(ResultSet.CLOSE_CURSORS_AT_COMMIT) parameter.
Well, SQL Server and Oracle are different databases. Their processing differs dramatically in some aspects. So having a code working well on one of them doesn't mean it will work as well on the other. I'm just guessing here, but if you're (knowingly or unknowingly) relying on commits to close resources, it might make your code usable on SQL Server, but unusable on Oracle.
You should still consider Chris's advice. SQL is quite a powerful language, it is possible that you could incorporate your business logic into it.
That looks innocuous (I have lots of code along similar lines and it works well). But the devil could lie in the detail. Could you post the full code? The business logic needn't be there, but every database operation must be included.
By the way, why did you try to use ResultSet.CLOSE_CURSORS_AT_COMMIT, if there are no commits inside the loop?
@ Martin Vajsar : Actually, I retrieve data from the result set, send it to a third party application and store the response in a temporary table. After that i am merging both main and temporary table. We use Oracle, Derby and SQL server databases. So the merging mechanism changes base on the database configured..
No need to apologize for not being able to post the code. But it makes it harder for someone to help you, of course.
The description of your wider process seems reasonable to me. But I don't understand the details. The pseudocode you've posted is the insertion phase, or the merge phase?
In any case, I'd advice to remove the commits, at least when using Oracle database. They are really not necessary. In truth, I've never tried to carry a ResultSet through a commit, so I actually don't know what happens. I'd also suggest to make sure you're using the latest Oracle JDBC drivers.