Cursor sensitivity example: An updated row

Consider the following sequence of events:

1. An application opens a cursor on the following query against the sample database.

   SELECT EmployeeID, Surname
   FROM Employees;

   EmployeeID	Surname
   102	Whitney
   105	Cobb
   160	Breault
   ...	...

2. The application fetches the first row through the cursor (102).

3. The application fetches the next row through the cursor (105).

4. A separate transaction updates the employee ID of employee 102 (Whitney) to 165 and commits the change.

The results of the cursor actions in this situation depend on the cursor sensitivity:

• Insensitive cursors – The UPDATE is not reflected in either the membership or values of the results as seen through the cursor:

  Action	Result
  Fetch previous row	Returns the original copy of the row (102).
  Fetch the first row (absolute fetch)	Returns the original copy of the row (102).
  Fetch the second row (absolute fetch)	Returns the unchanged row (105).

• Sensitive cursors – The membership of the result set has changed so that row 105 is now the first row in the result set:

  Action	Result
  Fetch previous row	Returns SQLCODE 100. The membership of the result set has changed so that 105 is now the first row. The cursor is moved to the position before the first row.
  Fetch the first row (absolute fetch)	Returns row 105.
  Fetch the second row (absolute fetch)	Returns row 160.

  In addition, a fetch on a sensitive cursor returns a SQLE_ROW_UPDATED_WARNING warning if the row has changed since the last reading. The warning is given only once. Subsequent fetches of the same row do not produce the warning.

  Similarly, a positioned update or delete through the cursor on a row since it was last fetched returns the SQLE_ROW_UPDATED_SINCE_READ error. An application must fetch the row again for an update or delete on a sensitive cursor to work.

  An update to any column causes the warning/error, even if the column is not referenced by the cursor. For example, a cursor on a query returning Surname would report the update even if only the Salary column was modified.

• Value-sensitive cursors – The membership of the result set is fixed, and so row 105 is still the second row of the result set. The UPDATE is reflected in the values of the cursor, and creates an effective "hole" in the result set.

  Action	Result
  Fetch previous row	Returns SQLCODE 100. The membership of the result set has changed so that 105 is now the first row: The cursor is positioned on the hole: it is before row 105.
  Fetch the first row (absolute fetch)	Returns SQLCODE -197. The membership of the result set has changed so that 105 is now the first row: The cursor is positioned on the hole: it is before row 105.
  Fetch the second row (absolute fetch)	Returns row 105.

• Asensitive cursors – For changes, the membership and values of the result set are indeterminate. The response to a fetch of the previous row, the first row, or the second row depends on the particular optimization method for the query, whether that method involved the formation of a work table, and whether the row being fetched was prefetched from the client.