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 47

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
ORA-01008: not all variables bound. 6 56
Read only access to a Procedure in oracle? 4 64
Oracle Listener Not Starting 11 44
Oracle DBLINKS From 11g to 8i 3 32
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 …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 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