WHAT IS A CURSOR?

The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work area is private to SQL’s operations and is called a Cursor.

The data that is stored in the cursor is called the Active Data Set. Conceptually, the size of the cursor in memory is the size required to hold the number of rows in the Active Data Set. The actual size, however, is determined by the Oracle engine’s built in memory management capabilities and the amount of RAM available. Oracle has a pre-defined area in main memory set aside, within which cursors are opened. Hence, the cursor’s size will be limited by the size of this pre-defined area.

The values retrieved from a table are held in a cursor opened in memory by the Oracle Engine. This data is then transferred to the client machine via the network. In order to hold this data, a cursor is opened at the client end. If the number of rows returned by the Oracle engine is more than the area available in the cursor opened on the client, the cursor data and the retrieved data is swapped between the operating system’s swap area and RAM.

Example:

When a user fires a select statement as:

SELECT EMP_NO, FNAME, DEPT FROM EMP_MSTR WHERE BRANCH_NO = ‘Bl’;

The resultant data set in the cursor opened at the Server end is displayed as under:

When a cursor is loaded with multiple rows via a query the Oracle engine opens and maintains a row pointer. Depending on user’s requests to view data the row pointer will be relocated within the cursor’s Active Data Set. Additionally Oracle also maintains multiple cursor variables. The values held in these variables indicate the status of the processing being done by the cursor.