ALLOCATE CURSOR

The ALLOCATE CURSOR statement defines a cursor and associates it with a result set locator variable.

Invocation

This statement can be embedded in an application program. It is an executable statement that can be dynamically prepared. It cannot be issued interactively. It must not be specified in REXX.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramALLOCATE cursor-nameCURSOR FOR RESULT SETrs-locator-variable

Description

cursor-name
Names the cursor. The name must not identify a cursor that has already been declared in the source program.
CURSOR FOR RESULT SET rs-locator-variable
Specifies a result set locator variable that has been declared in the application program according to the rules for declaring result set locator variables.
The result set locator variable must contain a valid result set locator value as returned by the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE SQL statement. The value of the result set locator variable is used at the time the cursor is allocated. Subsequent changes to the value of the result set locator have no effect on the allocated cursor. The result set locator value must not be the same as a value used for another cursor allocated in the source program.

Rules

  • The following rules apply when you use an allocated cursor:
    • You cannot open an allocated cursor with the OPEN statement.
    • You can close an allocated cursor with the CLOSE statement. Closing an allocated cursor closes the associated cursor defined in the stored procedure.
    • You can allocate only one cursor to each result set.
  • Allocated cursors follow the same rules as declared cursors in a program with the CLOSQLCSR option. The CLOSQLCSR option can be specified on the CRTSQLxxx command or using the SET OPTION statement.
  • A commit operation closes allocated cursors that are not defined WITH HOLD by the procedure and are not running with a commit level other than *NONE.
  • Closing an allocated cursor closes the associated cursor in the procedure.

Example

This SQL procedure example defines and associates cursor C1 with the result set locator variable LOC1 and the related result set returned by the SQL procedure:

    ALLOCATE C1 CURSOR FOR RESULT SET LOC1;