Difference between an implicit & an explicit cursor.

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.

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?

Ref:- sidharth

for x in (select * from amp)

loop

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

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

end loop;

is an explicit cursor.

hello,

i think this is an implicit cursor.

pavani.

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

No vandana

  that is explicit cursor

pavani

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

thank you

kamal

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;

/

No that is implicit cursor

coz it is used in begin part

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.

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

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.

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.

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.

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.

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

This is explicit cursor.

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

explicit

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.

@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

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

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.

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.