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

>>-ALLOCATE--cursor-name--CURSOR FOR RESULT SET--rs-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 on 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.

Examples

   ALLOCATE C1 CURSOR FOR RESULT SET LOC1;