Explicit Cursors RIP? Not Quite
Steven long ago issued a mea culpa for his bloomer. In fact he uses it now in his presentations as a classic example of why we should never take a guru's word for anything. We ought always to spike a test case to prove that it is true for our situation. Trust but verify.
The title of Misbah's article is "Explicit Cursors are Dead - Long live the Implicit Cursor". Is this true? Should we really never choose to use an explicit cursor rather than an implicit cursor? I can think of two clear cases when we still need explicit cursors. One is when we want to use BULK COLLECT INTO and the other is when we need to use a dynamic Ref Cursor. There is also the WHERE CURRENT OF clause for use with UPDATE and DELETE statements but I don't think it is mentioned in polite society anymore.
A fuzzier case is the use of a cursor attribute rather than trapping a
NO_DATA_FOUNDexception. I think exceptions ought to be raised for actual exceptions, that is non-standard states. So when I am expecting a query to return zero rows the proper thing is to use an explicit cursor and raise an exception if the cursor
%FOUNDattribute returns true. I think this is clearer about the intended meaning of the code than using an implicit cursor and a
NO_DATA_FOUNDhandler to handle the expected path. Of course, most of the time
NO_DATA_FOUNDis the exception and so the implicit cursor is usually the way to go.
In the comments Paul Driver says he still uses explicit cursors to avoid
TOO_MANY_ROWSexceptions being raised when the query returns duplicate rows. Personally I think this is a bug: SELECT ... INTO queries really ought to return a single row. If there is some very good reason why the query does return duplicate rows and we really don't care which row we get then our code should make this clear. Which is why Nature gave us the ability to filter by ROWNUM = 1.
So, does anybody out there still use for explicit cursors for regular querying? If so, what benefits do they offer over the performance of implicit cursors?