Implicit or Explicit cursor?

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
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.
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.
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
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".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.