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

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

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!