ALLOCATE CURSOR statement

The ALLOCATE CURSOR statement allocates a cursor for the result set identified by the result set locator variable.

For more information about result set locator variables, see the description of the ASSOCIATE LOCATORS statement.

Invocation

This statement can only be embedded in an SQL procedure. It is not an executable statement and cannot be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramALLOCATEcursor-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 SQL procedure (SQLSTATE 24502).
CURSOR FOR RESULT SET rs-locator-variable

Names a result set locator variable that has been declared in the source SQL procedure, according to the rules for declaring result set locator variables. For more information about declaring SQL variables, see Compound SQL (Procedure) statement.

The result set locator variable must contain a valid result set locator value, as returned by the ASSOCIATE LOCATORS SQL statement (SQLSTATE 0F001).

Rules

  • The following rules apply when using an allocated cursor:
    • An allocated cursor cannot be opened with the OPEN statement (SQLSTATE 24502).
    • An allocated cursor cannot be used in a positioned UPDATE or DELETE statement (SQLSTATE 42828).
    • An allocated cursor can be closed with the CLOSE statement. Closing an allocated cursor closes the associated cursor.
    • Only one cursor can be allocated to each result set.
  • Allocated cursors last until a rollback operation, an implicit close, or an explicit close.
  • A commit operation destroys allocated cursors that are not defined WITH HOLD.
  • Destroying an allocated cursor closes the associated cursor in the SQL 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;