' 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?
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?
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.
Shankara Sharma wrote:My businerss logic is.
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?
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.