ALLOCATE CURSOR

The ALLOCATE CURSOR statement defines a cursor for a dynamically-prepared statement or for a result set returned from a previously invoked procedure.

Syntax

►►──── ALLOCATE extended-cursor-name ──────────────────────────────────────────►

 ►┬─ CURSOR ──────┬─────────────────┬─ FOR extended-statement-name ────────┬───►◄
  │               ├ WITH RETURN ────┤                                      │
  │               └ WITHOUT RETURN ◄┘                                      │
  └┬──────────┬─ FOR PROCEDURE SPECIFIC PROCEDURE spec-routine-designator ─┘
   └─ CURSOR ─┘

Parameters

extended-cursor-name: Identifies the name of the cursor being defined. The name must conform to the rules for an identifier and must be unique within the specified scope.
extended-statement-name: Identifies the name of the statement for which the cursor is being defined. A statement with this name and scope must have been prepared within the same SQL transaction as that in which the ALLOCATE CURSOR statement is being executed.
WITH RETURN: Defines the cursor as a returnable cursor. If a returnable cursor is allocated in an SQL-invoked procedure and is in the open state when the procedure terminates, a result set is returned to the caller.
WITHOUT RETURN: Specifies that the cursor is not a returnable cursor. This is the default.
FOR PROCEDURE SPECIFIC PROCEDURE: Specifies that the cursor is to be allocated for a result set returned by the invocation of the identified procedure. This type of cursor is called a received cursor.
spec-routine-designator: Identifies the SQL-invoked procedure.

Parameters for Expansion of spec-routine-designator

schema-name: Specifies the schema with which the procedure identified by procedure-identifier is associated.
procedure-identifier: Identifies a procedure defined in the dictionary.
host-variable: Identifies a host variable containing the name of the previously invoked procedure.
routine-parameter: Identifies a routine parameter containing the name of the previously invoked procedure.
local-variable: Identifies a local variable containing the name of the previously invoked procedure.
SCHEMA: Qualifies the procedure name with the name of the schema with which it is associated. This option is an extension to the SQL standard.
schema-name: Specifies the schema with which the procedure is associated.
host-variable: Identifies a host variable containing the name of the schema with which the previously invoked procedure is associated.
routine-parameter: Identifies a routine parameter containing the name of the schema with which the previously invoked procedure is associated.
local-variable: Identifies a local variable containing the name of the schema with which the previously invoked procedure is associated.

Note: For more information about using a schema name to qualify a procedure, see Identifying Entities in Schemas.

Usage

Updateable Cursors

The PREPAREd statement referenced in the ALLOCATE CURSOR statement must be a cursor-specification. The cursor created as a result of the ALLOCATE CURSOR statement, is updateable, if the cursor-specification is updateable.

Allocating a Received Cursor for a Result Set

If the ALLOCATE statement is used for a result set, then the procedure identified by spec-routine-designator must have been previously invoked by an SQL CALL or SELECT statement in the same transaction as that in which the ALLOCATE CURSOR statement is executed.

The result sets that the SQL-invoked procedure returns, form a list ordered in the sequence in which the cursors were opened by the procedure. When a received cursor is allocated, the following actions are taken:

The new cursor is associated with the first result set in the list of returned result sets.
The result set is removed from the list.
The cursor is placed in the open state.
The cursor is positioned at the same point at which the corresponding returnable cursor was left by the procedure.

If an SQL-invoked procedure has started multiple sessions, the sequence of returned result sets is by session, in the order in which the sessions were connected. Within each session, the result sets are sequenced by the order in which their cursors were opened.

A received cursor cannot be used to return a result set nor can it be referenced in a positioned update or delete statement.

Note: For more information about updateable cursors, see DESCRIBE.

Examples

Creating a Local Cursor

The following ALLOCATE CURSOR statement creates a local cursor called C1 and associates it with the local statement whose name is passed in :sname:

EXEC SQL
  ALLOCATE 'C1' CURSOR FOR :SNAME
END-EXEC

Creating a Global Cursor

The following ALLOCATE CURSOR statement creates a global cursor whose name is passed in :CNAME and associates it with the global statement whose name is passed in :SNAME:

EXEC SQL
  ALLOCATE GLOBAL :CNAME CURSOR FOR :SNAME
END-EXEC

Sharing a Statement Definition

The following two ALLOCATE CURSOR statements create two cursors, one of which is local and one of which is global. They are both associated with the same local statement:

EXEC SQL
  ALLOCATE 'C1' CURSOR FOR 'S1'
END-EXEC
EXEC SQL
  ALLOCATE GLOBAL CURSOR 'G1' FOR 'S1'
END-EXEC

Allocating a Received Cursor for a Result Set

exec sql
      call GET_EMPLOYEE_INFO(1003)
end-exec
exec sql
      allocate 'RECEIVED_CURSOR_GET_EMPG' for procedure specific
      procedure GET_EMPLOYEE_INFO
end-exec