Insensitive Scroll Cursor
Insensitive Scroll Cursor
Insensitive scroll cursor is the cursor that is not sensitive to INSERT, UPDATE, or DELETE operations performed on the database table after generating the result table. i.e., after opening the cursor. For example, when the positioned UPDATE, DELETE, or INSERT is performed, the changes are not visible in the result table.
Declaring a cursor as INSENSITIVE has the below results -
- Rows inserted into the original table are not added to the result table after the cursor opens.
- The result table is read-only.
- FOR UPDATE clause is not allowed in the cursor. i.e., the cursor can't be used for the positioned update or delete operations.
Syntax -
EXEC SQL DECLARE cursor-name INSENSITIVE SCROLL CURSOR FOR select-statement END-EXEC.
INSENSITIVE -
- Specifies the cursor is not sensitive to insert, update, or delete operations performed on the database table after the result table is created.
- If it is coded, the cursor is read-only, and the result table is formed when the cursor is opened.
- The FOR UPDATE clause should not be used as it is a read-only cursor.
SCROLL -
- SCROLL specifies the cursor is scrollable.
- If the cursor is scrollable, the cursor can scroll forward, backward, and repositioned at the beginning, at the end, or the relative offset position.
- Scrollable cursors are especially useful for screen-based applications (CICS screens with page-up and page-down logic).
Example -
Input -

Scenario1 - Declare the INSENSITIVE SCROLL CURSOR for reading employee_details table.
Code -
...
EXEC SQL
DECLARE E1 INSENSITIVE SCROLL CURSOR FOR
SELECT EMP_NAME,
MANAGER_ID
FROM EMPLOYEE_DETAILS
FOR READ ONLY
END-EXEC.
...
Scenario2 - Declare the insensitive scroll readonly cursor for designation "FRESHER" in department 1000 of employee_details table.
Code -
...
EXEC SQL
DECLARE E2 INSENSITIVE SCROLL CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DESIGNATION = "FRESHER"
AND DEPT_ID = 1000
FOR READ ONLY
END-EXEC.
...