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.
idmscu19
The ALLOCATE CURSOR statement defines a cursor for a dynamically-prepared statement or for a result set returned from a previously invoked procedure.
This article describes the following information:
2
2
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.
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.
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