Implicit or Explicit cursor?

Posted on 2009-05-19
Last Modified: 2013-12-07
Is this an implicit cursor or explicit cursor? and why? Also what is the advantage of explict cursor over implicit cursor ie what we can do with explicit cursor which can not be done with implicit cursor?

Also can you please let me know where we need only strong cursor? I know a strong cursor requires return type. But I would like to know where we required to use only strong cursors and weak cursors can not be used?
for x in (select * from emp)
end loop;

Open in new window

Question by:GouthamAnand
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24423173
The one in the code you provided is not a Cursor construct. It is a FOR Looping Construct.

An explicit cursor is the one which is specified with Declare, Open, Fetch Cursor, Close Cursor.
More info about Implicit and Explicit Cursors below:

By Default, Oracle handles all request as Cursors. If you dont use Explicit cursors then the operations will be handled as implicit cursors.
LVL 48

Expert Comment

ID: 24423432
It is one of the mystic questions in Oracle.

Ten yeras ago the opinion (and the instructions) was to use explicit cursors.

But after some years the Oracle Tom Kyte advised to use implicit cursors (FOR ...SELECT)
as more efective. Steven Feuerstein - also.
LVL 16

Expert Comment

ID: 24423467
In your example, Oracle will create its own cursor to handle the retrieval of rows from the select statement.  The statement will therefore use an implicit cursor.

A cursor is only Explicit when you explicitly declare it and handle it in your code as described by rrjegan17.
LVL 16

Accepted Solution

Milleniumaire earned 500 total points
ID: 24423713
As schwertner says, it used to be advised that explicit cursors be used rather than implicit cursors.  As a result I still tend to use explicit cursors - old habbits die hard!

The reason back then was due to the extra fetch that was required to be performed by an implicit cursor to determine if more than one row would be retrieved, which would then raise an exception.  These days, Oracle is clever enough not to have to perform additional fetches and so the advantage of handling cursors yourself is no longer necessary.

In terms of declaring reference cursors, strong cursors are defined to return a type and so can be parsed at runtime.  Weak reference cursors don't know what the type is until they are used at runtime and so they tend to be more flexible in their use.  The advantage of strong reference cursors is that the compile can determine whether or not the developer has properly matched up the cursor variable's fetch statements with its cursor object's query list.  If used in a package, for example, you would get an error when trying to compile the package if a strong reference cursor was used incorrectly.  You wouldn't know if a weak cursor was used incorrectly until runtime.
LVL 35

Expert Comment

by:Mark Geerlings
ID: 24426164
Basically an implicit cursor has syntax like: "select ...into... from... where...".   And, it is not explicitly declared before it is used.

An explicit cursor has no "into..." clause and must be explicitly declared before it is used.

I agree that explicit cursors used to be more efficient (at runtime, that is) even though they required more lines of code.  Now, implicit cursors are as efficient as explicit cursors, and they require fewer lines of code, so it may seem like implict cursors are better.  However, implicit cursors can still raise either the "no data found " or "too many rows" exceptions, so you have to code for these possible exceptions (unless you are confident your code will never encounter them, but that may be a dangerous assumption).  So, I still use mainly explicit cursors since they don't raise either of these exceptions.

Here is an example of a function with an implict cursor:

create or replace function my_impl_function (v_value iin varchar2) return varchar2 as
  v_desc some_table.description%type;
  select description
  into v_desc
  from some_table
  where key_column = v_value;
  return v_desc;

Here is a function with an explicit cursor:
create or replace function my_expl_function (v_value iin varchar2) return varchar2 as
  v_desc some_table.description%type;
  cursor c1 is select description
    from some_table
    where key_column = v_value;
  open c1;
  fetch c1 into v_desc;
  close c1;
  return v_desc;

Note that I did not add an "exception" section in my first function, so this could raise either a "no_data_found" or a "too_many_rows" exception, depending on the records in "some_table" and the input parameter passed in "v_value".

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
join 2 views with 5 conditions 3 61
Create table from select - oracle 6 54
Use of Exception to end a Loop 3 45
Create Index on a Materialized View 5 35
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question