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.

---