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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

630 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