This discussion is archived
3 Replies Latest reply: Mar 4, 2013 5:21 AM by BillyVerreynne RSS

Difference between open-fetch-close and cursor forloop

SB2011 Newbie
Currently Being Moderated
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
    munky Guru
    Currently Being Moderated
    >
    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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points