Micro Focus Enterprise Developer 3.0 for Visual Studio 2013 > Programming > Mainframe Programming > SQL Option for DB2 > Reference > SQL Reference > SQL Statements > ALLOCATE CURSOR

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.

Authorization

No authorization is required for this statement.

Syntax

ALLOCATE CURSOR cursor-name 
CURSOR FOR RESULT SET rs-locator-variable

Parameters:

`cursor-name`	Names the cursor. The name must not identify a cursor that has already been declared in the source program.
`rs-locator-variable`	Names a result set locator variable that has been declared in the application program. The result set locator variable must contain a valid result set locator value, as returned by the ASSOCIATE LOCATORS or DESCRIBE PROCEDURE statements. In the ALLOCATE CURSOR statement, `rs-locator-variable` is always a host variable.

Description

When an ALLOCATE CURSOR statement is dynamically prepared, the EXECUTE statement with the USING clause must be used to execute the prepared statement. As with all dynamically prepared statements, parameter markers (question marks) must appear where the host variables would appear in the prepared statement. The USING clause of the EXECUTE statement should specify the host variables for which the values are to be substituted for the parameter markers in the dynamically prepared ALLOCATE CURSOR statement.

You cannot use a statement identifier for an ALLOCATE CURSOR statement if the same statement identifier has been used for a DECLARE CURSOR statement.

The following rules apply when you use an allocated cursor:

For this statement to be successful, an application must be currently connected to the site where the stored procedure was executed.
You cannot open an allocated cursor by using the SQL OPEN cursor statement.
You can close an allocated cursor by using the SQL CLOSE cursor statement. This closes the cursor in the stored procedure as well.
You can allocate only one cursor to each result set.

Rollback, and an implicit and explicit close destroy allocated cursors. A commit destroys allocated cursors that are not defined WITH HOLD by the stored procedure. Destroying an allocated cursor closes the associated cursor in the stored procedure.