The COMMIT or SYNCPOINT commands will close all opened cursors when they are executed. While declaring the cursor, the WITH HOLD option is used to avoid the above issue.

The cursor will close automatically if the WITH HOLD option is not coded. These cursors are called cursors WITHOUT HOLD. WITHOUT HOLD option is the default, and there is no need to code explicitly.

Syntax -

EXEC SQL
	DECLARE cursor-name [WITHOUT HOLD]
	    CURSOR FOR 
		select-statement
END-EXEC.

WITHOUT HOLD -

• Specifies the cursor can close when the COMMIT or SYNCPOINT executed.

Example -

Input -

Scenario1 - Declare the WITHOUT HOLD CURSOR for updating all columns of the employee_details table.

Code -

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

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

Code -

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

---