3 Replies Latest reply on Sep 15, 2004 12:49 PM by 82532

    does using a REF CURSOR do an implicit BULK fetch?

    213738
      We are using OO4O in our VB code to execute a stored proc which has a cursor variable of type REF CURSOR as an OUT parameter.

      When we execute it from the client, is data being fetched in batches, or all at once?

      I cannot BULK COLLECT into my cursor variable.

      -- Sridhar
        • 1. Re: does using a REF CURSOR do an implicit BULK fetch?
          82532
          It is fetched as needed (and prefetching is not applicable in REF CURSORS). Here as-needed means one at a time.

          • 2. Re: does using a REF CURSOR do an implicit BULK fetch?
            213738
            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!
            • 3. Re: does using a REF CURSOR do an implicit BULK fetch?
              82532
              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.