OR 11.2 | Open Statement

Language Reference Guide > Language Reference Guide > Statements > OpenROAD SQL Statements > Open Statement

Was this helpful?

Open Statement

This statement opens a cursor in the database, evaluates the specified statement, and associates the select statement's result table with the cursor.

This statement has the following syntax:

open cursor_variable for select_statement
[for update_clause];

where update_clause is one of the following:

readonly [deferred | direct] update of column{, column}

The open statement performs the following actions:

• Opens a cursor in the database

• Evaluates the specified select statement and positions the cursor immediately before the first row in the select statement's result table

• Sets the State attribute to CS_OPEN

You must use the open statement before you can use any data manipulation statements (fetch, update, or delete) with the cursor. After the cursor is open, it stays open until you close it with the close statement or until the transaction terminates.

The select statement retrieves a set of rows from the database. These rows are then associated with the cursor. (The set of rows is called a result table.) The target list of the select statement can use any of the following formats:

resultname = dbexpression
dbexpression as resultname
*
correlation_name.*
dbexpression

Note: The asterisk selects values from all columns.

See Select Statement for a complete description of its syntax. If you intend to specify the "for update" clause (described in the next section) in your open statement, its associated select statement can reference only one table and cannot contain a distinct, group by, having, order by, or union clause.

Parameters--Open Statement

This statement has the following parameters:

cursor_variable

Specifies the name of a local or global reference variable that points to an object of type CursorObject. This variable cannot have a null value.

select_statement

Specifies any legal select statement. If you open the cursor for update, its associated select statement can reference only one table.

update_clause

Defines the operations that you intend to perform on the rows retrieved by the select statement. This clause can have any of the following formats:

readonly [deferred | direct] update of column{, column}

column

Specifies the name of a column in the table that you intend to update. You cannot update a column that is not included in this list.

How You Can Define the Cursor Mode

The for update_clause defines the cursor mode. You can open a cursor in either of the following modes:

Read-only

Specifies that you can read the result table rows but not update or delete them

Update

Specifies that you can read, update, or delete rows in the result table

If you omit this clause, you can read or delete the result table rows but you cannot update them.

You can use the deferred and direct keywords to specify when updates and deletions are visible in the database. If you specify a deferred update cursor, then any updates or deletions made are not visible in the database until the cursor is closed. If you specify a direct update cursor, then any changes or deletions are visible immediately. If you do not specify one of these keywords, the default is deferred update.

If you are running under the default transaction management state (autocommit is off), you can open one deferred cursor and any number of direct update cursors and any number of read‑only cursors. If you are running with autocommit on, then you can have only one cursor open at any one time, regardless of its mode.

For more information about autocommit and transaction management, see the Programming Guide.

For more information about using cursors in OpenROAD applications, see Fetch Statement and the Programming Guide.

Examples--Open Statement

The following example opens emp_cursor as a read-only cursor, using the current value of the dept# variable in the where clause of the select statement:

open emp_cursor for select name, age

     from emp
     where dept = :dept#
     for readonly;

The following example opens emp_cursor so that the user can update the age column:

open emp_cursor for select name, age

     from emp
     where dept = :dept#
     for direct update of age;

Last modified date: 12/20/2023