OCI prefetching does not work for scrollable cursors

I am unable to get prefetching to work in my OCI code when using scrollable cursors. I am using Oracle Server 9.2.0.7, and the OCI manual for 9i states that prefetching does work for scrollable cursors.  An Oracle whitepaper on scrollable cursors says the same.

In pseudo code, here is what I do:

OCIStmtPrepare2()
OCIAttrSet(OCI_ATTR_PREFETCH_ROWS = 20)
OCIStmtExecute()
OCIStmtFetch2()
..
..

OCIStmtRelease()

All calls return successfully, no errors of any sort happen, but in my tracefile I see that every record is fetched one by one from the server. This is killing me on roundtrips. I have also tried setting OCI_ATTR_PREFETCH_MEMORY to 50mb, but still no success. Moving the OCIAttrySet() call to be between any other functions gives the same results.

I do not want to do array fetching because pre-fetching should be able to accomplish basically the same thing transparent to my app, so my code avoids complexity. Does anyone know any reason why this prefetching does not work? Received on Mon Dec 04 2006 - 15:29:32 CST