CLOSE

CLOSE is the last step of cursor processing. It releases all resources used by the cursor.

What CLOSE does

• Releases all resources (memory, locks held for the cursor) used by the cursor.
• After CLOSE, the cursor is no longer positioned on any row - FETCH is not allowed until the cursor is OPENed again.
• CLOSE does not delete or change any data - it only ends the cursor's use of the result table.
• CLOSE is coded in the Procedure Division, usually in its own paragraph like CLOSE-PARA.
• A cursor that is CLOSEd can be OPENed again later in the same program run - the SELECT will execute fresh with current host variable values.

Syntax and example

• Syntax: EXEC SQL CLOSE <cursor-name> END-EXEC.
• Example - CLOSE when the FETCH loop ends:
  
  FETCH-PARA. EXEC SQL FETCH C1 INTO :DCLDEPT END-EXEC. IF SQLCODE = 0 DISPLAY DNO DNAME ELSE IF SQLCODE = 100 PERFORM CLOSE-PARA ELSE DISPLAY "FETCH ERROR " SQLCODE STOP RUN END-IF END-IF. CLOSE-PARA. EXEC SQL CLOSE C1 END-EXEC.
• Check SQLCODE after CLOSE too - 0 means the cursor closed cleanly.
• Place the CLOSE where the loop naturally ends (on SQLCODE = 100), and also in the error path so a failed program does not leave the cursor open.

CLOSE best practices

• Always CLOSE every cursor you OPEN, even when an error occurs - use an error paragraph that closes open cursors before stopping.
• Do not CLOSE a cursor twice in a row - the second CLOSE fails because the cursor is already closed.
• If you need to re-run the cursor with new values: CLOSE, change the host variables, then OPEN again.
• In long-running programs, CLOSE cursors as soon as you are done with them to free locks and memory.
• A COMMIT also closes cursors unless they are declared WITH HOLD - so do not FETCH after a COMMIT on a normal cursor.