This content has been marked as final. Show 3 replies
It is fetched as needed (and prefetching is not applicable in REF CURSORS). Here as-needed means one at a time.
Actually Mark, I just came across an option to set the "fetchlimit" when we create the plsqldynaset in VB. I played with it a little bit today, and changing that value does seem to have an effect.
If it were row-by-row, as your email indicates, it would be horrendous for remote users(say Europe users accessing US database) executing stored procedures.
Also, in my research on this topic, I was reading Tom Kyte's "effective oracle by design" where he does a comparion of bulk collect and ref cursor returining data to a client via a java program. And, the ref cursor was performing better, and he indicates that the data is fetched in "batches" based on fetch setting.
Let me know if I'm interpreting this all wrong. Thanks!
Trust me, it is row-by-row at the oci level. What OO4O is doing for you is building a cache by fetching a number of rows at a time (but each row one at a time). The OCI prefetch settings don't currently affect a REF CURSOR. There is also a slightly greater overhead due to extra meta-data that is generated when using a REF CURSOR, but generally this is small in relation to the cursor data and has little impact.