Explicit cursor and Select into statement

What is the difference between an explicit cursor and select into statement?

Questions by varma.ukk

Showing Answers 1 - 6 of 6 Answers

neppalli

  • Oct 3rd, 2008
 

Explicit cursors are those user defined cursors in order to retrive more than one row from a table. Select into statment helps in invoking parmeters into the formal parameters.

  Was this answer useful?  Yes

kz4ora

  • Nov 8th, 2008
 

Explicit cursor- They are defined by programmer.

The select statement can return multiple rows as a query result. If you want to process single row at a time then you can define an explicit cursor for this select statement.

Cursor is  like a temporary area where you can process fetch the records and process then individually as required

select into statement-
select value1,value2... into var1,var2......

When a cursor name is explicitly assigned to a SELECT statement through CURSOR statement, it is called an explicit cursor. Explicit cursors are generally used to work with more than one row within PL/SQL. It has got the following attributes:
1. Defining the Cursor
2. Opening the Cursor
3. Fetching rows(values) from Cursor (one at a time)
4. Closing the Cursor

SELECT into statement allows you to retrives the query result in user defined variables. But the limitation here is that the query must return a single row. Its a best practice to handle NO_DATA_FOUND and TOO_MANY_ROWS exception when dealing with "SELECT INTO" statement.

  Was this answer useful?  Yes

manveshv

  • Dec 30th, 2009
 

A Select..into statement is even an implicit cursor concept. But the disadvantage is it can't handle if the select statement returns more than 1 row.

So, implicit cursor opens before the statements executed and closed after automatically and hence it is preferred when a select statement returns one row or for using DML operations.

Explicit Cursor needs to be opened, fetch rows and close explicitly. This can be used when we need to process each and every statement the select query returns.

rashlikesu

  • May 12th, 2010
 

When select into query returns just one row, Oracle uses implicit cursor to execute and fetch the results for the query.

But if the select statement returns more than one row, an Explicit cursor is created to fetch the data.

  Was this answer useful?  Yes

Select statement can return multiples rows at a time and there exists only one network round trip.


Explicit cursors are memory areas which acts as a handle or pointer to context area and allows to fetch and process query results row by row .because of row by row processing there will me more network round trips. 

  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.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions