Explicit Cursor Vs Implicit Cursor
September 19, 2007 by Mark Marucot
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 explained the uses of explicit cursor.
Explicit cursor is the best way to use in PL/SQL programs in the following instance. If we are creating programs that will be use for a long period of time because many developers may come and go that will provide system development, production support and system enhancement. We must admit that noone can stay long that will handle your system. If your system has limited documentation, explicit cursor is much better because of the following reason.
Readability
The PL/SQL program is more readable because of the separation of data selection logic and program logic which involves data handling. Explicit cursors are defined in declaration section and cursor names are usually descriptive which helps other developers to understand the SQL statement. In the execution block, once the explicit cursor is open, the developer can only see the how the data is handled and not mixed up with any SQL statements from implicit cursors.
Flexible Error Handling
In error handling, explicit cursor provides more flexibility by using %NOT_FOUND, %ROWCOUNT, % IS_OPEN. This allows error handling logic done in the execution block and leave the Exception block for other unhandled error.
Debugging Error is Easier
Since explicit cursor is defined, the cursor names can be use in error message in error handling logic which makes it easier to determine where the error occurred.
When to use Implicit Cursor?
As a developer implicit cursor is the fastest way to retrieve records. Implicit cursors can be use in the following cases.
Creating supporting scripts Implicit cursor is applicable if you want to accomplish your task immediately such like creating support scripts. Support scripts is just an aid to help developers and usually a developer’s personal file. Only the developer himself knows the logic of the support script.
Creating One-Time Use Program
One-time use programs as usually data conversion and migration scripts. If you are migrating data from legacy system to the new system, data conversion scripts are used once and no longer needed anymore. You can also use explicit cursors for these type of programs.
No matter which is better between the explicit and implicit cursor. It’s up to the developer’s point of view which is readable, traceable and easier to use.
Comments
Feel free to leave a comment...
and oh, if you want a pic to show with your comment, go get a gravatar!