Reopening a Select or Function Cursor

The database server evaluates the values that are specified in the USING clause of the OPEN statement only when it opens a Select cursor or Function cursor. While the cursor is open, subsequent changes to program variables in the USING clause do not change the active set of the cursor.

In a database that is ANSI-compliant, you receive an error code if you try to open a cursor that is already open.

In a database that is not ANSI-compliant, a subsequent OPEN statement closes the cursor and then reopens it. When the database server reopens the cursor, it creates a new active set, based on the current values of the variables in the USING clause. If the variables have changed since the previous OPEN statement, reopening the cursor can generate an entirely different active set.

Even if the values of the variables are unchanged, the values in the active set can be different, as in the following situations:
  • If the user-defined function takes a different execution path from the previous OPEN statement on a Function cursor
  • If data in the table was modified since the previous OPEN statement on a Select cursor

The database server can process most queries dynamically, without pre-fetching all rows when it opens the Select or Function cursor. Therefore, if other users are modifying the table at the same time that the cursor is being processed, the active set might reflect the results of these actions.

For some queries, the database server evaluates the entire active set when it opens the cursor. These queries include those with the following features:
  • Queries that require sorting: those with an ORDER BY clause or with the DISTINCT or UNIQUE keyword
  • Queries that require hashing: those with a join or with the GROUP BY clause

For these queries, any changes that other users make to the table while the cursor is being processed are not reflected in the active set.