The OPEN statement executes the SELECT statement specified when the cursor was declared and positions the cursor immediately before the first row returned. (To actually retrieve the rows, use the FETCH statement.)

A cursor must be opened before it can be used in any data manipulation statements (such as FETCH, UPDATE, or DELETE) and a cursor must be declared before it can be opened.

When a cursor that was declared for a dynamically prepared SELECT statement is opened, use the USING clause if the prepared SELECT statement contains constants specified with question marks. For information about using question marks to specify constants in prepared statements, see Prepare (see PREPARE).

The USING clause provides the values for these “unspecified” constants in the prepared SELECT so that the OPEN statement can execute the SELECT. For example, assume that your application contains the following dynamically prepared SELECT statement:

When opening the cursor for this prepared SELECT statement, values for the question marks must be provided in the WHERE clause. The USING clause performs this task. For example:

The values in the low and high variables replace the question marks in the WHERE clause and the DBMS Server evaluates the SELECT statement accordingly. If an SQLDA is used, the values that replace the question marks are taken from variables to which the sqlvar elements point. Before using the descriptor in an OPEN CURSOR statement, allocate the SQLDA and the variables to which the sqlvar elements point, and place values in the variables. For more information about the SQLDA and its sqlvar elements, see the chapter “Working with Embedded SQL.”

More than one cursor can be opened at the same time, but only one cursor that has been declared for update in deferred mode can be open at a time. If a cursor that has been declared for update in deferred mode is open, all other open cursors must have been declared for readonly or for update in direct mode.

The same cursor can be opened and closed (with the CLOSE statement) successive times in a single program. An open cursor must be closed before it can be reopened.

A string constant or a host language variable can be used to specify the cursor_name. The open statement must be terminated according to the rules of your host language.