We have little descriptive information on the cursor working in the documentation. They are explained below,
1. When we declare a Cursor what really happens in the back end.
The Oracle server allocates a chunk of private temporary workspace in the SGA called the context area for processing every single SQL statement encountered inside a PL/SQL block. This memory area holds the SQL query, its parsing information and the result set returned by the SQL query. The result set can then be processed either row by row or in bulk. As the context area is managed internally by the Oracle server, we do not have any control over it. The cursor can hold n number of rows, but can process only one row at a time. The record set held by the cursor is called as the active set.
2. When we open a cursor will the query get executed, if yes where would the result get stored in memory.OPEN
At this stage, the cursor is initialized and allocated with a portion of the private memory of the server process in the Process Global Area associated with the session for processing the SQL statement. During this stage, the cursor points to the allocated context area. This stage internally follows the below stages in the below-mentioned sequence.
1) PARSE: This is the primary step for processing the cursor associated SQL statement by undergoing the syntax and semantic checking. The syntax check verifies for any misspelled keywords. The semantic check mainly verifies for the object permission and for its validity. After this step, the binding process takes place, which is mentioned in the below step.
2) BIND: This step replaces all the bind variables present in the SQL statement with their actual value and gets it ready for the execution. If the bind value passed is not valid, no check is performed during the binding process, but a runtime error is thrown during the program execution.
3) EXECUTE: This phase executes the resulting SQL query from the above step, fetches the corresponding data from the database and loads them up in the pre-allocated context area. This process additionally sets up the record pointer to the first row of the query’s result set.
3. What happens when we fetch the data from cursor. Is that fetching from the result set or does the actual execution takes place during cursor fetch.FETCH
This stage pulls the data from the query result set stored in the context area in the above step. The cursor pointer is incremented by one row and points the next row in the result set sequence for every single row fetch. This stage also fetches rows in bulk using the BULK COLLECT clause and the number of rows to be fetched can be limited using the LIMIT clause. Once the cursor pointer reaches the last row of the result set, the pointer halts, and any further fetch statement returns only the last row from the result set.