ALLOCATE CURSOR statement Format 2 (Allocate a result set cursor)
Function
Allocates a cursor to a group of ordered result sets that were returned from a procedure.
Privileges
Format 2: Allocating a cursor to the group of result sets returned from a procedure
ALLOCATE extended-cursor-name FOR
PROCEDURE [authorization-identifier.]routine-identifier |
Operands
Specifies the extended cursor name for the cursor to be allocated.
For extended cursor names, see 2.27 Extended cursor name.
- [authorization-identifier.] routine-identifier
- authorization-identifier
- Specifies the authorization identifier of the owner of the procedure that returned the result set to which a cursor is to be allocated.
- routine-identifier
- Specifies the name of a routine in the procedure that returned the result set to which a cursor is to be allocated.
Common rules
- Specify a procedure that has already been called in the current SQL session (from the time HiRDB is connected to the time it is disconnected).
- If a procedure specified in the current SQL session is called two times or more, the cursor is allocated to the group of result sets that was returned by the last called procedure.
- An error may occur if a cursor is already allocated to the group of result sets returned by the last called procedure among the specified procedures.
- The following return codes are assigned if a specified procedure does not return any result sets:
- A return code 100 to the SQLCODE area of the SQL communications area
- A return code 100 to the SQLCODE variable
- A return code 02001 to the SQLSTATE variable
- When the ALLOCATE CURSOR statement is executed, the cursor references the first result set among the result sets returned by the procedure, and it can fetch the data in the result set using a FETCH statement. The CLOSE statement is executed to reference the second result set and beyond. Upon execution of the CLOSE statement, if any of the following return codes is assigned, it indicates that another result set exists, and the cursor references the next result set:
- A return code 121 to the SQLCODE area of the SQL communications area
- A return code 121 to the SQLCODE variable
- A return code 0100D to the SQLSTATE variable
On the other hand, upon execution of the CLOSE statement, if any of the following return codes is assigned, it indicates that another result set does not exist, in which case the extended cursor name does not identify any cursor:
- A return code 100 to the SQLCODE area of the SQL communications area
- A return code 100 to the SQLCODE variable
- A return code 02001 to the SQLSTATE variable
- The definition of the allocated cursor is the same as the cursor declaration in the procedure that generated the result set being referenced.
- The allocated cursor remains open.
- The allocated cursor is located at the cursor position that would be in effect when the procedure terminated.
- Updates and deletions using a cursor cannot be performed using an SQL statement that specifies a foreign table or a view table based on a foreign table.
Notes
- An error may occur if a specified extended cursor name is already allocated in the prevailing scope.
Examples