I am calling a store procedure from my java class that returns me a ref cursor, I am extracting out the result set from the cursor and iterating the result set for my use, now my question is this
is the returned ref cursor has all the data in it or it again goes to the database server when the getObject is called on the cursor for result set ?
Fetch Size is the Oracle parameter you are looking for. It is a very important setting if your application involves transferring data from the Server to a Client.
You can set the fetch size for your statement using this method.
void setFetchSize(int rows) throws SQLException
http://download.oracle.com/docs/cd/B28359_01/java.111/b31224/resltset.htm#sthref1000
From the link above...
By default, when Oracle JDBC executes a query, it receives the result set 10 rows at a time from the database cursor. This is the default Oracle row-prefetch value. You can change the number of rows retrieved with each trip to the database cursor by changing the row-prefetch value (see "Oracle Row Prefetching" for more information).
When the statement object executes a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.