Solved

Implicit or Explicit cursor?

Posted on 2009-05-19
5
877 Views
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)
 

loop

..................................;

...............................;

end loop;

Open in new window

0
Comment
Question by:GouthamAnand
5 Comments
 
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:
http://hell.org.ua/Docs/oreilly/oracle/prog2/ch06_03.htm

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

Expert Comment

by:schwertner
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.
0
 
LVL 16

Expert Comment

by:Milleniumaire
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.
0
 
LVL 16

Accepted Solution

by:
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.
0
 
LVL 34

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;
begin
  select description
  into v_desc
  from some_table
  where key_column = v_value;
  return v_desc;
end;
/

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;
begin
  open c1;
  fetch c1 into v_desc;
  close c1;
  return v_desc;
end;
/

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".
0

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.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now