Difference between an implicit & an explicit cursor.

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.
Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.
  • Interview Candidate
  • 31
  • 56748

Showing Answers 1 - 31 of 31 Answers

Rajasekar

  • Apr 2nd, 2006
 

When use SELECT INTO implicit cursor, it should return one row and exactly one row. Otherwise, exception will be thrown as no data found or returing more than one row.

  Was this answer useful?  Yes

siddhartha saha

  • Aug 23rd, 2006
 

begin

for x in (select * from emp) loop

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

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

end loop;

end;

Just tell me which type of cursor that is being used in the above example?

Is explicit or implicit?

  Was this answer useful?  Yes

paul

  • Sep 2nd, 2006
 

Ref:- sidharth

for x in (select * from amp)

loop

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

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

end loop;

is an explicit cursor.

  Was this answer useful?  Yes

pavani

  • Sep 6th, 2006
 

hello,

i think this is an implicit cursor.

pavani.

  Was this answer useful?  Yes

Paul Marconi

  • Oct 4th, 2006
 

It is an implicit cursor because there is no 'cursor' keyword.

  Was this answer useful?  Yes

kamal

  • Oct 9th, 2006
 

No vandana

  that is explicit cursor

  Was this answer useful?  Yes

kamal

  • Oct 9th, 2006
 

pavani

  actually in that programme selection statement having * that is why that is explicit cursor.

thank you

kamal

  Was this answer useful?  Yes

rampratap409

  • Oct 9th, 2006
 

Implicit cursor will select and return only (only) one row

like :

select ename, job into v_ename, v_job

from emp

where empno = 7839;

Explicit cursor can have one row or multiple

like in previous example its explicit because statement may return more then one row.

another example for explict cursor:

declare

cursor c1 is select ename, job, sal from emp;

v_ename emp.ename%type;

v_job emp.job%type;

v_sal emp.sal%type;

begin

open c1;

loop

fetch c1 into v_ename, v_job, v_sal;

exit when c1%notfound;

dbms_output.put_line(v_ename||'  '|v_job||'  '||v_sal);

end loop;

end;

/

  Was this answer useful?  Yes

vishal

  • Nov 17th, 2006
 

No that is implicit cursor

coz it is used in begin part

  Was this answer useful?  Yes

Murali Krishna Nekkalapudi

  • Nov 25th, 2006
 

Implicit cursors are created and closed by the PL/SQL engine itself. Implicit cursors are given the name SQL. However explicit cursors are user defined.

  Was this answer useful?  Yes

Nikhil_4_Oracle

  • Mar 9th, 2007
 



HI ALL,

Basic Funda,

Implicit Cursor is provided by Oracle,

select * from emp -->implicit cursor ,


Explicit Cursor is User defined cursor.


Hence Cursor above is Implicit cursor;


Bye


Nikhil


  Was this answer useful?  Yes

Srinivas02

  • May 10th, 2007
 

Whenever you execute an DELETE, UPDATE, INSERT or SELECT, the DBMS allocates a memory location called CONTEXT AREA.
For the SELECT statement the context area also stores the active set of data rows that the query retrieves.
An implicit cusrsor is a pointer that points to the context area.

By the above conditions the cursor is an implicit cursor.

Please correct me if there is a mistake.

  Was this answer useful?  Yes

syam sundar

  • Aug 17th, 2007
 

Implicit:
1. Implicit cursor declared by oracle itself
2. Implicit cursor does not controlled by user. It is controlled by oracle means cursor open, fetch and close are implicitly done by oracle itself, not by user
3. It returns single row data and does not return more than one row

Explicit:
1. Explicit cursor declared by user
2. It controlled by user means cursor opening , fetching and closing
3.
It can returns single row or multiple rows and if you want retrieve more than one row, you should go to explicit cursor.

Amit yadav

  • Sep 7th, 2007
 

how r u saying that implicit cursor always select only one row ?
please can any body give me expnaded part of (SELECT * FROM EMP;) because it is implicit cursor but it can display multiple rows.

  Was this answer useful?  Yes

Satish Haridass

  • Sep 13th, 2007
 

Thats rite implcit cursor are declared and managed by PL/SQL for all DML and PL/SQL select statements, they can return multiple rows. The Implict Cursor Name is SQL,
defacto:
SQL%ISOPEN is always false as the prompt is returned only when done
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT all hold gud.


Explcit cursor are managed by programmer and he gets a work area identified by the cursor name. the Work area is alllocated for processing his active set.

  Was this answer useful?  Yes

thirupathi

  • Sep 24th, 2007
 

We use explicit cursor because the loop selects more than one row. Implicit cursor is used for selecting only one row.

Send reply right or wrong

  Was this answer useful?  Yes

sarath

  • Oct 30th, 2007
 

This is explicit cursor.

  Was this answer useful?  Yes

Ravi Savaliya

  • Oct 30th, 2007
 

ya it  is a implicit cursor,
In order to user explicit cursor, you have to declare, open and fetch data from it.

I hope this will help u

  Was this answer useful?  Yes

narmadac

  • Feb 11th, 2010
 

Ref:- sidharth

BEGIN
FOR x IN (select * from amp)
LOOP
...............................;
...............................;
END LOOP;
END;

Is an Explicit cursor because for queries that return more than one row we use explicit cursor to access the rows individually.

  Was this answer useful?  Yes

Venkat Pallis

  • Dec 5th, 2011
 

@siddarth,narmadac

This is a basic funda. The prog which was given by references to Implicit cursor. Could just look at bulk loading concept. When we use bulk load in sql statement all records from a table will l retrieve as bulk. All records will be stored in memory area. At the of displaying it will send it as bulk to display all records in Context Area (Oracle). This is automatically done by oracle. ur not declaring any thing in deceleration part..

This references to implicit cursor

  Was this answer useful?  Yes

Mahesh Chinvar

  • Dec 16th, 2011
 

Implicit cursor also fetches return more than one row like explicit cursor but in implicit cursor we cannot pass parameters while in explicit cursor we can pass parameters

  Was this answer useful?  Yes

rohitosu

  • Jul 31st, 2012
 

A) A cursor is a pointer to the results of a query run against one of more tables in the database.

IMPLICIT CURSOR : PL/SQL declares and manages an implicit cursor every time you execute a SQL DML statement (INSERT, UPDATE, MERGE, or DELETE) or a SELECT INTO that returns a single row from the database directly into a PL/SQL data structure. This kind of cursor is called œimplicit” because the database automatically handles many of the cursor-related operations for you, such as allocating a cursor, opening the cursor, fetching records, and even closing the cursor (although this is not an excuse to write code that relies on this behavior).

Explicit Cursor : Explicit cursor is a SELECT statement that you declare as a cursor explicitly in your application code. You then also explicitly perform each operation against that cursor. (Open,fetch,close). You will generally use explicit cursors when you need to retrieve multiple rows from data sources using static SQL.

  Was this answer useful?  Yes

manjari_ghosh12

  • Sep 1st, 2012
 

Whenever a DML operation done there is a implicit cursor present. Explicit cursor is defined by user. Whenever a query runs implicit cursor automatically runs. User have no control on it and can not open or fetch from implicit cursor. Implicit cursor is useful to check some status. Like how much rows fetched/updated/deleted, any data found or not etc. PL/SQL implicitly declares a cursor for all SQL data manipulation statements on a set of rows, including queries that return only one row.

  Was this answer useful?  Yes

mohamd

  • Aug 6th, 2013
 

This is a implicit cursor.

  Was this answer useful?  Yes

plsqlgeek

  • Aug 21st, 2013
 

Explicit cursor. Here we do not require a cursor definition, open, fetch and close here because CURSOR FOR LOOP was used. You can use SQL%ROWCOUNT inside the loop to see how many rows it returns, if it were a implicit cursor. You will find NULL, thats because its an explicit cursor.

  Was this answer useful?  Yes

Deepesh

  • Aug 21st, 2013
 

Its an explicit cursor not only because it returns more than one row as a result, but because it is declared explicitly. Visually you wont see the orthodox CURSOR cursor_name IS ........ etc kind of declaration, but logically the for keyword declares x as a cursor and thus it is explicitly declared by programmer. We have complete control over x unlike the implicit cursor..... Its an explicit cursor.

  Was this answer useful?  Yes

VEERA

  • Jul 26th, 2014
 

Implicit cursors are controlled by the system and explicit cursors are controlled by the user.

  Was this answer useful?  Yes

Prakash

  • Jun 17th, 2015
 

Explicit cursor. It will return more than one rows

  Was this answer useful?  Yes

Justo

  • Jul 15th, 2015
 

Its an implicit cursor.

  Was this answer useful?  Yes

Manjunath

  • Sep 1st, 2015
 

Hello,
Given example PL/SQL program is comes under Implicit, Bcz
1. if its Explicit then cursor has to be declared in declarative part along with SELECT statement to retrieve the data from Database and to placed into CONTEXT AREA to be perform as an ACTIVE SET for user defined variable (cursor).
If Im not wrong,
Manjunath,
Pentaho BI Developer.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions