Forums Register Login

Can we divide a ResultSet Object to multiple result set objects?

+Pie Number of slices to send: Send
I want to divide a result set object to multiple result set objects? Is that possible? If so.. How?
+Pie Number of slices to send: Send
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.

What are you trying to achieve?
+Pie Number of slices to send: Send
You can put the individual results into Lists, Maps, etc, using some sort of filtering.
+Pie Number of slices to send: Send
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?
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
 

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?
+Pie Number of slices to send: Send
I need to perform some business logic on each record and then i need to insert back to another table in database..
+Pie Number of slices to send: Send
@ 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.
+Pie Number of slices to send: Send
My businerss logic is.

try
{
PreparedSattement statement = ...

While(resultSet.next())
{
Batch insertion for every 1000 records...
}
}
Catch()
{
Exception handling
}
finally()
{
Close result set.
Close prepared statement.
}

+Pie Number of slices to send: Send
 

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.
+Pie Number of slices to send: Send
 

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?
+Pie Number of slices to send: Send
@ 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..
+Pie Number of slices to send: Send
Hmmm... I can not post the code Martin . Sorry for that! But after every batch insertion i commit database.
+Pie Number of slices to send: Send
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.
+Pie Number of slices to send: Send
@Martin : The code snippet i posted is at insertion part.. Merge part is working fine...
Where all the women are strong, all the men are good looking and all the tiny ads are above average:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 3018 times.
Similar Threads
Using Java to convert mm to m, cm and mm
preferred collection class for storing Resultset?
ArrayList too slow?
writing packages
access
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 19, 2024 01:18:32.