Sometimes, the cursor should not close until the CLOSE CURSOR execution. However, executing the COMMIT or SYNCPOINT commands will close all opened cursors.

The WITH HOLD option is used to avoid the above issue while declaring the cursor. WITH HOLD prevents the cursor from closing when the COMMIT operation is performed before the CLOSE CURSOR execution. If the WITH HOLD option specifies, COMMIT only commits the current unit of work without closing the cursor.

Syntax -

EXEC SQL
	DECLARE cursor-name WITH HOLD
	    CURSOR FOR 
		select-statement
END-EXEC.

WITH HOLD -

• Specifies the cursor should not close when the COMMIT or SYNCPOINT executed.

Example -

Input -

Scenario1 - Declare the WITH HOLD CURSOR for updating column manager_id of employee_details table.

Code -

  EXEC SQL 
	DECLARE E1 CURSOR WITH HOLD FOR
     	SELECT EMP_NAME,
               MANAGER_ID
     	FROM   EMPLOYEE_DETAILS
        FOR    UPDATE OF MANAGER_ID
   END-EXEC.

Scenario2 - Declare the WITH HOLD CURSOR for updating all columns of the employee_details table.

Code -

   EXEC SQL 
	DECLARE E2 CURSOR WITH HOLD FOR
     	SELECT EMP_ID,
               EMP_NAME,
               DESIGNATION,
               MANAGER_ID,
               DATE_OF_HIRE,
               SALARY,
               DEPT_ID
     	FROM   EMPLOYEE_DETAILS 
     	FOR UPDATE 
   END-EXEC.

---