Life Cycle of a Cursor

Having a good understanding of life cycle of cursors is required knowledge for optimizing application
that execute SQL statements. The following are the steps carried out during the processing of a cursor:
Open cursor: A memory structure for the cursor is allocated in the server-side private memory
of the server process associates with the session,a user global area (UGA).Note that no SQL statement is associated with the cursor yet
Parse cursor: A SQL statement is associate with the cursor It's parse representation that include
the execution plan (which describe how the SQl engine will execute the SQl statement)is loaded in the shared pool ,specifically,in the literary cache. The structure in the uga is updated to store a pointer to the location of the sharable cursor in the library cache
Define output variables:  If the SQL statements returns a data , the variable receiving must be declare .This is not necessary not only for quires but also for DELETE,INSERT,UPDATE statement that use the returning clause
Bind input variables : If the SQL statement use bind variables, their value must be provided .
No check is performed during the binding.If invalid data is passed , a runtime error will be raised during the execution.
Execute cursor: The SQL statements is executed.But be careful , becuase the database engine doesn't always do anything significant during this phase. In fact, for many types of queries,

the real processing is usually delayed to the fetch phase.
Fetch cursor: If the SQL statement returns data, this step retrieves it. Especially for queries,
this step is where most of the processing is performed. In the case of queries, rows might
be partially fetched. In other words, the cursor might be closed before fetching all the rows.
Close cursor: The resources associated with the cursor in the UGA are freed and consequently
made available for other cursors.The shareable cursor in the library cache is not
removed. It remains there in the hope of being reused in the future.