Home » SQL & PL/SQL » SQL & PL/SQL » CURSOR FOR LOOP (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production)
CURSOR FOR LOOP [message #617378] Fri, 27 June 2014 20:30 Go to next message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

Hi,

I got some very basic doubt about CURSOR FOR LOOP, but got no reference of it how internally fetches rows.

Does it
1. Fetch all the rows at once, and stores it and may cause memory problem if data is very big?
2. Fetch row by row as required and execute statements inside body?
3. May fetch a chunk of records, and internally may usage bulk_collect to limit number of rows?

I think it's either 2, or 3 depending on the situation, can anyone please refer me to some manual, as I tried but was not able to find any reference.

Thanks,
Manu
Re: CURSOR FOR LOOP [message #617380 is a reply to message #617378] Fri, 27 June 2014 22:45 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
What will you do differently after your doubt is eliminated?
How will this new knowledge change how you interact with any Oracle database?

Remember that plain SQL is always faster than PL/SQL to produce the same results.
You should never use PL/SQL when plain SQL can product the same results.
Re: CURSOR FOR LOOP [message #617383 is a reply to message #617378] Sat, 28 June 2014 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

(3) Logically the cursor loop fetches one row at a time, actually cursor loop fetches 100 rows each time in 10g and 11g (don't know in 12c) but this is ONE of the implementations Oracle made and can change at any time.
So for us, it is row by row and if we need BULK COLLECT we have to write it.

Re: CURSOR FOR LOOP [message #617389 is a reply to message #617378] Sat, 28 June 2014 09:38 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
A good read http://awads.net/wp/2008/11/10/5-recommendations-about-cursor-for-loops-in-oracle-plsql/
Re: CURSOR FOR LOOP [message #617505 is a reply to message #617389] Mon, 30 June 2014 21:46 Go to previous message
manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA
Senior Member

That's fine BlackSwan, I was aware that it fetches 1 by 1 row at a time, and 10g/11g simulates bulk collect functionality internally up to some extent.

Because I was fetching from very large table, and if it fetches all the data same time and store it somewhere, then it will be problem.
But someone was too confident, that I have to check my knowledge again.

Lalit, appreciate your help. I read the same information on

http://www.oracle.com/technetwork/issue-archive/2008/08-nov/o68plsql-088608.html
and
http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html

Thanks Michel, that's exactly what I needed to know.

Manu

[Updated on: Mon, 30 June 2014 21:48]

Report message to a moderator

Previous Topic: Data extract from scheduled process improvement
Next Topic: DBMS_LOB defined in a View - Error: ORA-01031: insufficient privileges
Goto Forum:
  


Current Time: Fri Mar 29 05:41:13 CDT 2024