3 Replies Latest reply: Mar 4, 2013 7:21 AM by Billy~Verreynne RSS

    Difference between open-fetch-close and cursor forloop

    SB2011
      Hi All,

      Theoretically using OPEN-FETCH-CLOSE Cursor method and CURSOR-FOR-LOOP one can implement the same functionality.

      Only the difference is cusror-for-loop(implicit open,fetch,close) is a shortcut to the explicitly opening,then fetching and closing cursor method .

      Is there any other difference between the two and is there a scenario where we can use only OPEN-FETCH-CLOSE cursor and not the CURSOR-FOR-LOOP and vice versa.

      Thanks & Regards,
      SB2011

      Edited by: SB2011 on Mar 4, 2013 4:09 AM
        • 1. Re: Difference between open-fetch-close and cursor forloop
          Z?
          >
          Only the difference is cusror-for-loop(implicit open,fetch,close) is a shortcut to the explicitly opening,then fetching and closing cursor method .
          >
          No, its not.

          Have a read of this...

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4696422878211

          Cheers

          Ben
          • 2. Re: Difference between open-fetch-close and cursor forloop
            Purvesh K
            Similar discussion {message:id=3418062}.
            Searching would have been much more easier than posting a thread, isn't it?
            • 3. Re: Difference between open-fetch-close and cursor forloop
              Billy~Verreynne
              SB2011 wrote:

              Theoretically using OPEN-FETCH-CLOSE Cursor method and CURSOR-FOR-LOOP one can implement the same functionality.
              Incorrect. Mostly the same. But not exactly the same functionality wise.

              An explicit cursor fetch allows explicit bulk collection. Having a such a collection allows for explicit bulk binding (using the ForAll statement) in the cursor loop.

              An implicit cursor for loop does an implicit bulk collection, courtesy of PL/SQL optimisation. However, one does not have direct access to the collection in the cursor loop, and can thus not reference it for explicit bulk binding.

              A bit of a moot point in some respects as both methods would be pulling data from one SQL cursor, and sending that data back via another SQL cursor - which is far more effectively done using a single SQL cursor to do both the reading and writing of data.