• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

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

 
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I want to divide a result set object to multiple result set objects? Is that possible? If so.. How?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Marshal
Posts: 79151
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You can put the individual results into Lists, Maps, etc, using some sort of filtering.
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I need to perform some business logic on each record and then i need to insert back to another table in database..
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@ 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.
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
}

 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@ 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..
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hmmm... I can not post the code Martin . Sorry for that! But after every batch insertion i commit database.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Shankara Sharma
Ranch Hand
Posts: 48
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
@Martin : The code snippet i posted is at insertion part.. Merge part is working fine...
 
See where your hand is? Not there. It's next to this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic