| Oracle Cursors with java/jdbc - Millions of rows? [message #216884] |
Tue, 30 January 2007 15:04 |
svguerin3
Messages: 44
Registered: November 2005
Location: TX
|
Member |
|
|
I hope I can explain this "fairly simple" issue well:
We have a stored procedure that returns a SYS_REFCURSOR to our Java/Spring application. We currently are passing "start_value" and "fetchsize" values to the proc, and it loops in the java, to only fetch batches of 10,000 rows with each call to the proc/query. After we make the initial 10k call, we then create an output file from this data, then make another call to grab the next 10k, etc.. until all rows have been fetched and processed.
This has worked fine in the past with total rowcounts of 70k or less, but now it is becoming a problem with rowcounts of 1million+. We are having to go out and make the procedure-call potentially hundreds of times for this amount of data, when it seems like it should only make it once with one giant fetch.
What is the best solution to this issue?? If possible, we would like to keep the funcationality of the stored proc and still return a SYS_REFCURSOR.. But are there dangers with having millions of rows for processing from a cursor like that in the Java code (snapshot too old, etc) if we change it to just bring back ALL rows instead of the multiple 10k batches?
Thanks, and I hope this makes sense! I will look forward to a response, and my apologies if this issue has already been addressed in these forums.
-Vincent
[Updated on: Tue, 30 January 2007 17:49] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
