Explicit Cursors are Dead - Long live the Implicit Cursor
For all those who have learnt their SQL with the help of Steven Feuerstein, you will have learnt that Explicit Cursors were the most efficient way of retrieving Data.
However Tom Kyte of Oracle Magazine’s ‘Ask Tom’, has been recommending for some time to use Implicit Cursors. His analysis is that the more pl/sql you write the more code has to be interpreted and in doing so this slows down the process.
Well, I was shocked and having tried it for myself comparing the exact same SQL within an explicit cursor and an implicit cursor and then running TKPROF on the trace, Tom’s Thesis was correct.
Obviously, it is a bit messy embedding each Implicit Cursor in an Anonymous SQL Block for error trapping but that is the way to go. If this bedrock of Oracle PL/SQL development has now changed then what next!
January 23rd, 2007 at 7:23 pm
Misbah
Like you I also use explicit cursors in preference to implicit, and as you mentioned they used to be faster to retrieve data than implicit cursors. That has now changed with the later database releases and it seems implicit cursors are quicker than explicit ones. However I’m not convinced that they are easier to write than emplicit ones - once you have added all the error handling required.
Let me give an example, the other day I had a problem with an implicit cursor which failed on the “when others” exception handler. In short the HRMS lookup, GB_BANKS, the cursor was based on had two seeded rows for the Bank of Scotland bank. The implicit cursor exception handler did not check for the “too many rows” exception so hit the “when others” exception and errored. When I re-coded this cursor as a explicit cursor the duplicate rows will not be a problem, all I needed was to confirm the bank names existence.
That incident highlights the importance of including all possible exceptions that may be raised by the implicit cursor, even if they seem unlikely to occur. The explicit cursor does not require all this extra coding so, despite the speed handicap I still use them!
January 24th, 2007 at 7:07 pm
Hi Paul/Misbah,
why should I have to write a different exception handler when I use a implicit cursor compared to an explicit cursor? The only exception handler I could think of would be a NO_DATA_FOUND, but I think it will be the same amount of code for an explicit cursor, because there I have to write an IF cursor%NOTFOUND THEN.
About handling TOO_MANY_ROWS, I think each time you write a SQL statement (also for explicit cursors) where you don’t use the PK or a unique key to access your data you have to think about what should happen in case if multiple rows are found. In your case above if you don’t care you can add ROWNUM=1 to your sql statement. In the other cases it’s good that an exception is raised, because you program/data is wrong and it would maybe work with wrong data if it just uses the first row.
Patrick
January 25th, 2007 at 7:55 pm
> and as you mentioned they used to be faster to retrieve data than implicit cursors. That has now changed with the later database releases…
No, it was only ever in Forms that there was an extra network round trip, and that was in the days when everyone coded SELECT FROM DUAL all over the place anyway. I don’t think anyone has shown there was ever an inherent advantage to explicit cursors in stored PL/SQL (though possibly that is unfair since nobody has 7.0 around to test on any more).
I think 9i changed everything for PL/SQL. The extensions to the SQL language made it possible to so much more in set operations that the world of Cursor FOR loops seems to belong to an earlier century
June 24th, 2007 at 9:16 am
[…] There are a lot of discussions in Oracle experts which is better between explicit cursor and implicit cursor. In Oracle Contractors, implicit cursors are sometimes recommended by Tom Kyte of Oracle Magazine’s ‘Ask Tom’ in contrary to Steven Feuerstein teaching that explicit cursors were the most efficient way of retrieving Data. Eddie Awads explained that implicit cursors will make the program shorter yielding the same result. On the other side of the river bank, Andrew Clarke believes that Explicit Cursor is the way to go. […]