The moose likes JDBC and Relational Databases and the fly likes Huge data, problem iterating resultset Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Huge data, problem iterating resultset" Watch "Huge data, problem iterating resultset" New topic

Huge data, problem iterating resultset

az ziz
Ranch Hand

Joined: Dec 17, 2008
Posts: 50
deaar all,

for the reporting purpose, i retrieve a huge data from oracle db using JDBC, invoke a procedure and get a cursor. as i iterate the cursor with ResultSet, i call one more procedure to get the details of this result(unique id). all are put into the appropriate beans and stored into the arraylist. by this time, I get transaction roll back exception if the data is huge, for example if the data exceeds 1000.

the structure is like this

call proc_1(param)
rs1 = get from proc_1 //this is the one that has thousands of records

iterate(rs1) {
id = rs1(1);
//map to beans

call proc_2(id)
rs2 = proc_2 // comes in tens
iterate(rs2) {
//map to beans

call proc_3(id)
rs3 = proc_3 // count mostly below 5
iterate(rs3) {
//map to beans
//add bean to arraylist

//get the complete list

the above pseudo code of the total iteration. now i tried changing the procedure to retrieve all the results using inner cursors in single call to the db. i iterate the inner cursors the same way, except that i wont call the procedures (proc_2 and proc_3) explicitly. but anyway the problem was not solved.

Thanks in advance for your helps

Paul Sturrock

Joined: Apr 14, 2004
Posts: 10336

What does the exception say?

JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Deepak Bala

Joined: Feb 24, 2006
Posts: 6662

transaction roll back exception

Post the full stack trace. Is it a timeout ?

for the reporting purpose

What sort of reporting requirement requires you to store the entire data into an arraylist ? Can your requirement not be satisfied by BIRT / Crystal reports etc etc ?

SCJP 6 articles - SCJP 5/6 mock exams - More SCJP Mocks
I agree. Here's the link:
subject: Huge data, problem iterating resultset
jQuery in Action, 3rd edition