Home » SQL & PL/SQL » SQL & PL/SQL » how does a cursor work?
how does a cursor work? [message #229435] Sat, 07 April 2007 16:51 Go to next message
Messages: 10
Registered: February 2007
Junior Member
I've been using pl/sql cursors for a long time already, but when asked how it works internally, I get a blurred idea. Here are some of my queries:
1. I know that cursor is a pointer to a memory location. But what does that location contain: the SQL statement or the rows returned by the cursor's query?
2. When I open a cursor, does it fetch the record one-by-one into the memory(?) or cache OR does it fetch all records into the memory and reads from there as I iterate?
3. If it places the rows into the memory, then the cursor should have a limited data size it can return, right??
I would also appreciate if you can give me any link/webpage that details how a cursor work.. Thanks.
Re: how does a cursor work? [message #229436 is a reply to message #229435] Sat, 07 April 2007 17:23 Go to previous messageGo to next message
Messages: 25542
Registered: January 2009
Location: SoCal
Senior Member
It appears you are unwilling or incapable of doing ANY independent research or even try to Read The Fine Manual; such as

Statement Handles or Cursors
A cursor is a handle or name for a private SQL area in which a parsed statement and other information for processing the statement are kept. (Oracle Call Interface, OCI, refers to these as statement handles.) Although most Oracle users rely on automatic cursor handling of Oracle utilities, the programmatic interfaces offer application designers more control over cursors.

For example, in precompiler application development, a cursor is a named resource available to a program and can be used specifically to parse SQL statements embedded within the application. Application developers can code an application so it controls the phases of SQL statement execution and thus improves application performance.

Re: how does a cursor work? [message #229447 is a reply to message #229436] Sun, 08 April 2007 02:06 Go to previous messageGo to next message
Messages: 10
Registered: February 2007
Junior Member
I read a few articles (may not be all that are available in the internet), and they all commonly say the same - it's a pointer or handle to a memory (or in your reply, 'private SQL area') -but none that I found talk about how the records are fetched. This one says that
Cursors provide a means to retrieve multiple rows into a buffer (when you OPEN the cursor) that can then be traversed sequentially (FETCH) to retrieve individual rows—until there is no more data (cur_%NOTFOUND becomes true).

- if that's the case, what does 'multiple' mean here? ALL? some rows only - if so how many? if ALL, is there a limit - thousand rows? millions? by row-size or number of rows??? The info I get are so limited..

Re: how does a cursor work? [message #229459 is a reply to message #229447] Sun, 08 April 2007 07:13 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
When you OPEN a cursor, Oracle does all of the operations necessary to fetch the first row, but does not fetch it. Each time you FETCH, Oracle returns exactly how many rows you asked for - they are not kept in memory.

Consider the cursor:
SELECT * FROM big_table

This will OPEN very quickly; it just parses the statement and takes out a shared lock on the table. When you FETCH (say a single row), it finds the first row and fetches it. About the only thing that happens in memory is that it changes its record of "where I am up to" in the table.

If you have a cursor that has a sort or join operation, it will take a bit more work to OPEN the cursor.

Consider the cursor:
SELECT * FROM big_table ORDER BY 1

This will retrieve (but not "fetch") every row from big_table, sort them in memory, paging to disk if necessary, and then return control to the calling SQL. Whatever memory/disk was required to store the sorted results prior to fetching will be held until the cursor is closed. So, as you fetch it doesn't take up extra memory, but nor does it release any either.

So yes, a cursor can blow your memory/temp space, but not because it is remembering or pre-fetching all of the rows' just because a sort or join operation was too big for the database environment.

Also note that there is a lot of stuff - mostly stuff I don't understand - going on in the background that I haven't mentioned. This is as good a description of it as I have ever seen.

Ross Leishman
Re: how does a cursor work? [message #229466 is a reply to message #229459] Sun, 08 April 2007 11:27 Go to previous message
Messages: 10
Registered: February 2007
Junior Member
Thanks, Ross, that's pretty helpful.
Previous Topic: Update statement - How do i remove "-" from field
Next Topic: Error...........
Goto Forum:

Current Time: Wed Jul 26 14:12:10 CDT 2017

Total time taken to generate the page: 0.16535 seconds