Huge data, problem iterating resultset

[az ziz]

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

Vijay

[Paul Sturrock]

What does the exception say?

[Deepak Bala]

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 ?