Which DB?
We are using Teradata.
First of all, thank you "Stu Derby" for proposing a solution.
If by "retrieve the ResultSet in chunks", you mean some way to explicitly limit the size of ResultSet to a fixed size and repeat execution to get back a ResultSet that will give you different rows each time, then no.
Well, thats exactly, what i wanted. Suppose it had 10000 rows and i set a fetch size of 500. One first invocation, i need to get the 1-500 rows then on second invocation 501-1000 rows and so on, which i think is not possible with the approach mentioned by "Stu Derby".