This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Apr 18, 2013 10:04 PM by BillyVerreynne RSS

How cursor works....?

astir Newbie
Currently Being Moderated
I have gone through a lot of documentation on Cursors, but could not get a clear picture about how it (cursors) works, apart from the fact that.............

They are the handlers to the output of the sql/dml statements executed.
They hold/refer to the data held at the SQL/context area.
They process one row at any given time. (Here I wanted to know, does that mean, else, n numbers of rows are processed at a time.)
and all other details about cursors like....open, close , cursor attributes etc. But I am not getting clear idea about "*behind the scenes*" of cursors.

A link would do, if my queries takes a lot of ur time.

Thanks in advance,
  • 1. Re: How cursor works....?
    Chandrakaanth Ramamurthy Explorer
    Currently Being Moderated
    A cursor can be considered as a temporary memory allocation for a query to be executed at runtime

    How are cursor Operates?

    Let us consider we are using a cursor with the traditional syntax (Declaration of cursor,open,fetch,close)

    In the first step the cursor is declared, when we say 'Open cursor'
    A space in the memory is allocated for the execution of the cursor.

    When fetch is executed that is when oracle instructs to execute the SQL statement and return the rows.

    When the Close is executed the memory is cleared.

    All these steps are automated when we use the Cursor For loop.

    Hope this gives some clarity
  • 2. Re: How cursor works....?
    Hoek Guru
    Currently Being Moderated
    Times change.
    I usually stick to implicit cursors (if necessary using a FOR loop) or REF cursors and (bulk) collections.
    Explicitly opening/fetching/closing is just soooo 1980's....(and usually slow-by-slow 'heritage') ;)

    A few searches on http://asktom.oracle.com will give you new insights, just like the articles from Oracle Magazine:
    http://www.oracle.com/technetwork/issue-archive/2007/07-mar/o27asktom-084983.html
    http://www.oracle.com/technetwork/issue-archive/o63asktom-082127.html (scroll down a bit)

    Mr. PL/SQL, Steven Feuerstein, also wrote about it more than once:
    http://www.oracle.com/technetwork/issue-archive/2013/13-mar/o23plsql-1906474.html
    http://www.oracle.com/technetwork/issue-archive/2008/08-nov/o68plsql-088608.html
  • 3. Re: How cursor works....?
    EdStevens Guru
    Currently Being Moderated
    Chandrakaanth Ramamurthy wrote:
    A cursor can be considered as a temporary memory allocation for a query to be executed at runtime

    How are cursor Operates?

    Let us consider we are using a cursor with the traditional syntax (Declaration of cursor,open,fetch,close)

    In the first step the cursor is declared, when we say 'Open cursor'
    No, the cursor is declared when we say
    CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
      IS select_statement;
    see http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#BABHEBAI
    A space in the memory is allocated for the execution of the cursor.
    The space is allocated in memory when we OPEN the cursor, not when we DECLARE it.


    >
    When fetch is executed that is when oracle instructs to execute the SQL statement and return the rows.
    No again. SQL is executed when we OPEN the cursor. In fact, opening the cursor does the following:

    - Allocates database resources to process the query
    - Processes the query; that is:
    - Identifies the result set
    - Positions the cursor before the first row of the result set

    (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#CIHBJJJH)



    The FETCH statement retrieves the current row of the result set, stores the column values of that row into the variables or record, and advances the cursor to the next row.

    (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#BABJIICC)

    >
    When the Close is executed the memory is cleared.
    That one is correct.

    (http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/static.htm#CIHBJJJH)

    All these steps are automated when we use the Cursor For loop.

    Hope this gives some clarity
    Disclaimer: My above comments are for explicit cursors.

    Edited by: EdStevens on Apr 16, 2013 1:11 PM
  • 4. Re: How cursor works....?
    Chandrakaanth Ramamurthy Explorer
    Currently Being Moderated
    @Ed
    I think the written communication wasn't clear, the ',' and the line i posted the message has confused the interpreter i believe

    I meant that memory is allocated when the open cursor is executed.

    But i did attend an oracle training where they had told me that only when the fetch command is executed oracle will execute the query and navigate the result set.
  • 5. Re: How cursor works....?
    EdStevens Guru
    Currently Being Moderated
    Chandrakaanth Ramamurthy wrote:
    @Ed
    I think the written communication wasn't clear, the ',' and the line i posted the message has confused the interpreter i believe

    I meant that memory is allocated when the open cursor is executed.

    But i did attend an oracle training where they had told me that only when the fetch command is executed oracle will execute the query and navigate the result set.
    then either the trainer was wrong, or you misunderstood what you thought he said. Did you read the links I provided?
  • 6. Re: How cursor works....?
    Chandrakaanth Ramamurthy Explorer
    Currently Being Moderated
    Yes, i did, i have made a note on that point as well.
  • 7. Re: How cursor works....?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    astir wrote:
    I have gone through a lot of documentation on Cursors, but could not get a clear picture about how it (cursors) works
    A cursor (Oracle speak) is the same as a DLL (Window speak) or a Shared Object (Linux/Unix speak).

    Simple terms. A program (which is what a cursor is - compiled SQL source code) has a code segment and data segment. The code segment is loaded once and shared - allowing many to use it. However, each caller using that program code, needs its own private data segment. This explained by the Shared Pool and Private SQL Area/Context.

    A DLL/Shared Object provides a public interface containing procedures/functions to call. A cursor public interface includes calls like fetch, close, describe, bind, and so on.

    Read up on DLLs. The same concepts apply to a cursor.

    And no. A cursor is not a set of results (data) in memory. So referring to a cursor as a resultset, is not correct. A cursor outputs data. It follows standard behaviour of all programs - input (bind), processing (fetch), output (rows returned).
  • 8. Re: How cursor works....?
    BluShadow Guru Moderator
    Currently Being Moderated
    Take a read of this article on ref cursors which may also help...

    {thread:id=886365}

    and this other one I wrote on Cursors and SQL Projection, which shows a general breakdown of what goes on with cursors (all SQL queries are cursors regardless of the code that processes them - some of which can do it 'all in one' and some which does it step by step)...

    {thread:id=2309172}
  • 9. Re: How cursor works....?
    Hesh Newbie
    Currently Being Moderated
    read about architecture, hmm... big deal !! isn't it?.. OK, try to digest slowly, bit by bit.... read about memory components PGA and SGA. As every query is a cursor, I would suggest to understand what will happens in memory constructs of oracle database when a query is executed.

    I will risk to point following...

    [http://it.toolbox.com/blogs/confessions/why-oracle-works-the-way-it-does-7-the-pga-9294]
  • 10. Re: How cursor works....?
    riedelme Expert
    Currently Being Moderated
    astir wrote:
    I have gone through a lot of documentation on Cursors, but could not get a clear picture about how it (cursors) works, apart from the fact that.............

    They are the handlers to the output of the sql/dml statements executed.
    They hold/refer to the data held at the SQL/context area.
    They process one row at any given time. (Here I wanted to know, does that mean, else, n numbers of rows are processed at a time.)
    and all other details about cursors like....open, close , cursor attributes etc. But I am not getting clear idea about "*behind the scenes*" of cursors.

    A link would do, if my queries takes a lot of ur time.

    Thanks in advance,
    Check the docs about how to use the DBMS_SQL package. The steps you manually perform to perform a SELECT reflect what Oracle does automatically when you use an ordinary cursor
  • 11. Re: How cursor works....?
    BluShadow Guru Moderator
    Currently Being Moderated
    riedelme wrote:
    astir wrote:
    I have gone through a lot of documentation on Cursors, but could not get a clear picture about how it (cursors) works, apart from the fact that.............

    They are the handlers to the output of the sql/dml statements executed.
    They hold/refer to the data held at the SQL/context area.
    They process one row at any given time. (Here I wanted to know, does that mean, else, n numbers of rows are processed at a time.)
    and all other details about cursors like....open, close , cursor attributes etc. But I am not getting clear idea about "*behind the scenes*" of cursors.

    A link would do, if my queries takes a lot of ur time.

    Thanks in advance,
    Check the docs about how to use the DBMS_SQL package. The steps you manually perform to perform a SELECT reflect what Oracle does automatically when you use an ordinary cursor
    Also demonstrated in my second link. ;)
  • 12. Re: How cursor works....?
    Hoek Guru
    Currently Being Moderated
    Just another explanation, quite short this time, so pretty easy to digest ( and remember ;) ) :
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:580621300346132183
  • 13. Re: How cursor works....?
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    I know what Tom is trying to convey, but if you mention a file handle people tend to think that a cursor is some kind of in-memory-file - and start treating and thinking of cursors as result/data sets that are like temp tables in memory ("+oe!, i want to join this cursor result set with that cursor result set+"). Which creates another set of problems.

    Always an issue when teaching - the balance between being too technical and too superficial. Never mind that I always hated drawing up lesson plans for the next day... :-)
  • 14. Re: How cursor works....?
    astir Newbie
    Currently Being Moderated
    Thanks a ton to alllll..........for all your valuable time and articulates on cursors......My understanding on cursors is clear and illustrated below.......

    Cursors -
    1. A pointer / handle pointing to the SQL (DML) query results.
    2. When a cursor is declared, it allocates memory in the SQL private area/context area which is residing in the PGA.
    3. Once a cursor is opened using OPEN statement, SQL statement is executed with corresponding memory space being allocated in PGA. The cursor points to the beginning of the first row in the result set held in the DB buffer cache.
    4. When a FETCH statement is used, the cursor fetches the first row to the SGA from the DB buffer cache and does the necessary processing. And the cursor moves to the beginning of the second row.
    5. CLOSE statement closes the cursor and flushes off the memory.

    I am clear as far as the concept is concerned (my understandings...as illustrated above), but yet I am confused on, when the memory is allocated
    a. When a cursor is declared
    b. after OPEN, when a SQL statement is executed
    Programmatically, I go by 'a', because, if we think of a cursor as a variable in a program while declaring, memory is allocated only when a variable is declared (concept followed from ages).
    Conceptually, I go by 'b', because, since a cursor is a pointer, it has nothing to do with memory unless a SQL statement is fired.

    Above all, does this memory allocation differs from implicit cursors to explicit cursors.
    Please help me out of this confusion.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points