was a bestseller. 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_FOUND exception. 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 %FOUND attribute returns true. I think this is clearer about the intended meaning of the code than using an implicit cursor and a NO_DATA_FOUND handler to handle the expected path. Of course, most of the time NO_DATA_FOUND is 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_ROWS exceptions 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?
10 comments:
Hi Andrew,
what a coincident, I had the same thoughts yesterday when I read the comment of Paul. Seems that the comment hasn't been published till today. Good to see that somebody has the same thought about how code should look like.
Patrick
There is a good section on this issue in "Mastering Oracle PL/SQL" by Connor McDonald (Apress). Personally, I use explicit cursors most of the time because (a) that is how I started out with Pl/SQL years ago(!), and (b) it makes it much easier to encapsulate the SQL, package it, re-use it, read the control flow around a big SQL statement, etc. You can do much of this with implicit cursors, but as explicit cursors let you do it really easily, why bother re-inventing this particular wheel?
A good reason to use implicit cursors whenever possible is reduce this type of mess...
IF my_cur%ISOPEN THEN
CLOSE my_cur;
OPEN my_cur;
ELSE
OPEN my_cur;
END IF;
FETCH my_cur into my_rec;
or
exception
when others then
if my_cur%ISOPEN then
CLOSE my_cur;
end if;
raise;
"I can think of two clear cases when we still need explicit cursors. One is when we want to use BULK COLLECT INTO"
Is that still valid with the feature in 10G that does automatic bulk collect?
Hi Andrew,
You are certainly right that you need to use explicit cursors with WHERE CURRENT OF and with REFCURSORS. However, you can still use BULK SELECT INTO implicitly as shown by these examples: -
http://www.adp-gmbh.ch/ora/plsql/bc/index.html
I really appreciate your input and I and all the other Editors on the Oracle Contractors Blog are happy to receive all input from capable individuals such as yourselves: -
http://blog.oraclecontractors.com/?cat=3
IF my_cur%ISOPEN THEN
CLOSE my_cur;
OPEN my_cur;
ELSE
OPEN my_cur;
END IF;
FETCH my_cur into my_rec;
Yeah, but this code is a bit of a mess anyway. If you closed down your cursor in a controlled manner last time you used it, you don't need to try to close it before you re-open it here. If this is inside a LOOP, then a cursor FOR LOOP is much easier to read than any messing about with implicit cursors. If this is a single-row SELECT i.e. then it is precisely the sort of thing that people most often tend to use an implicit cursor for anyway (only many people often to forget to check for TOO_MANY_ROWS or NO_DATA_FOUND).
exception
when others then
if my_cur%ISOPEN then
CLOSE my_cur;
Again, this is not always necessary. If the explicit cursor is declared locally, then it will be closed as soon as you leave the procedure anyway.
I really recommend Chapter 3 "The Vexed Subject of Cursors" in Connor McDonald's "Mastering Oracle PL/SQL" (APress) for a revealing discussion of the pros and cons of both kinds of cursor. It's very much a case of horses for courses.
And all else being equal, I still think it's easier to follow the flow of a process if the SQL (which in a real system might be 100 lines or more) is encapsulated by the explicit cursor instead of embedded in the middle of your code.
>> However, you can still use BULK SELECT INTO
>> implicitly as shown by these examples: -
Funnily enough, that particular thought popped into my mind over the weekend. Of course if we want to use the LIMIT clause then we still need to use the explicit cursor.
Cheers, APC
good post !- I have always used explicit cursors - I am trying to change those habits now (one reason for using explicit cursors is that I feel secure that I will capture all exceptions).
>> one reason for using explicit
>> cursors is that I feel secure that
>> I will capture all exceptions
I would argue that explicit cursors suppress the TOO_MANY_ROWS exception rather than capture it. Whether it matters depends upon what your program is intending to do.
I know what you're saying about old habits dying hard. Me too. However, I have found there is less typing involved in using implicit cursors, so I rely on laziness to lead me into better coding practices :D
Cheers, APC
Hello,
I have sometimes encountered cases, with Forms applications for instance, where I could get the infamous TOO MANY OPEN CURSORS message because of too much implicit cursors in the Forms.
Select Into... never closes the cursor since you have provided a COMMIT or ROLLBACK statement.
Francois
Post a Comment