Hi,
on other thread when i am discussing about some other topic i got the following response on the cursor. I want to have more understanding on cursor.
Billy Verreynne wrote:
All SQL that are received by Oracle is parsed as cursors and stored in the shared pool. Oracle provides a reference handle/pointer to the caller to interact with this cursor that was created for the client.
PL/SQL is such a client. It provides different client data types to deal with this SQL cursor handle. You can use the SQL cursor handle via an explicit PL/SQL variable. Or via a DBMS_SQL variable. Or via a refcursor variable.
Oracle does not care what the client calls this SQL cursor handle, or how the client uses the handle and what interfaces it provides for client code. All SQL cursors are equivalent to Oracle - irrespective of how the client deals with that SQL cursor.
A SQL cursor is also not a data set or result set. Memory is not allocated and the results of the cursor stored in it. This is not a scalable approach when dealing with 1000's of cursors. There is simply not enough memory for such a flawed approach.
A SQL cursor is a program. It is executable code - the execution plan of the cursor describes this executable program. The steps that the cursor will execute. The cursor, like any programs, provides output. This output is fetched by the client. A good SQL cursor also supports input - in the form of bind variables. This allows the same program to be used and re-used. For example, a cursor is created for select * from emp where empid = :1. The input data to the cursor is bind variable 1 that contains the employee id. The output of the program is an employee row. The same program can be used by a 1000 clients at the same time - a single copy of that cursor/program is all that needs to reside in server memory, as oppose to a 1000 cursors that all do the same thing.
So i came to know that SQL returns Cursor, will be stored in shared pool and the reference of the cursor will be returned to the client. So every time the client asks for next record the cursor executes the SQL, Pulls the entire records and order them if required and returns the next row. Is this the way i have to understand? Does this process happens every time i try to pull the next record in the cursor? I am trying to understand in the performance point of view. If cursor does not store the set of records how does it give me the next record? How do i understand this?
Edited by: Muralidhar on Jan 26, 2011 10:48 PM