Home » Other » Training & Certification » Cursors
Cursors [message #258495] Sun, 12 August 2007 14:48 Go to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
Hi Team,

I had faced the following question in an interview recently.
whats the best option for this question

Q)Suppose i have a to select a field from a table ,
which is the best to use
1)Select into or 2)Explicit cursor and the reason for the same


Thanks In Advance
Binu





Re: Cursors [message #258496 is a reply to message #258495] Sun, 12 August 2007 15:03 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
neither
Re: Cursors [message #258497 is a reply to message #258496] Sun, 12 August 2007 15:09 Go to previous messageGo to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
Could you be more clear on this
Re: Cursors [message #258516 is a reply to message #258495] Sun, 12 August 2007 22:55 Go to previous messageGo to next message
TimJF
Messages: 11
Registered: August 2007
Junior Member
I'm no expert, but I reckon it depends whether or not you are going to fetch more than one row from the database.

If you are certain that you will only return the value from one row, than a 'SELECT INTO' would be fine.

Otherwise, given the choices you have listed, I would use an explicit cursor.

Hope this helps.
Re: Cursors [message #258518 is a reply to message #258516] Sun, 12 August 2007 23:28 Go to previous messageGo to next message
swapnajojo
Messages: 40
Registered: June 2007
Location: India
Member
Thanks Friend for your reply
Re: Cursors [message #258548 is a reply to message #258497] Mon, 13 August 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL.

Regards
Michel
Re: Cursors [message #258597 is a reply to message #258548] Mon, 13 August 2007 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Since they bothered asking it in an interview, the answer they were most likely searching for was Explicit Cursor, because SELECT INTO performs an extra fetch to ensure that it returns only one row.

For PL/SQL, this is a myth. Oracle does not perform the extra fetch; SELECT INTO is just as fast as an explicit cursor.

For the pre-compilers, it could be a factor though. Depending on one of the compiler options, SELECT INTO may perform an extra fetch in Pro*C, Pro*COBOL, etc. This may also be the case with OCI - I'm not sure.

Assuming you are using PL/SQL, the only considerations are:
- whether you want to protect against TOO_MANY_ROWS exception
- whether you want to share the cursor and open/fetch it in many places.

If you want to protect against TOO_MANY_ROWS, you can either select on a unique key or add AND ROWNUM = 1 to your SELECT INTO query. Both of these are probably tidier that using an explicit cursor.

If you want to share the SQL, you must use an explicit cursor.

So, to sum up: You could tell the interviewer that explicit cursors should only be used when SQL needs to be shared, and that it is a common misconception that Explicit Cursors out-perform Implicit Cursors. That might start an argument - depends how convincing you can sound.

Ross Leishman
Re: Cursors [message #258599 is a reply to message #258495] Mon, 13 August 2007 02:55 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Thanks Rleishman. Nice demonstration.
Previous Topic: Oracle Apps Institute in Delhi
Next Topic: Packages
Goto Forum:
  


Current Time: Wed Dec 07 14:22:22 CST 2016

Total time taken to generate the page: 0.07669 seconds