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

None.

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
Specifies the authorization identifier of the owner of the procedure that returned the result set to which a cursor is to be allocated.
If the cursor is allocated for a public procedure, specify upper-case PUBLIC enclosed in double quotation marks (") as the authorization identifier.
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

  1. 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).
  2. 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.
  3. 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.
  4. 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
  5. 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
  6. The definition of the allocated cursor is the same as the cursor declaration in the procedure that generated the result set being referenced.
  7. The allocated cursor remains open.
  8. The allocated cursor is located at the cursor position that would be in effect when the procedure terminated.
  9. If the cursor is allocated for a public procedure, specify upper-case PUBLIC enclosed in double quotation marks (") as the authorization identifier.

Notes

  1. An error may occur if a specified extended cursor name is already allocated in the prevailing scope.

Examples

See 1.9.3 Results-set return facility for examples.