---

OPEN FOR Statement

The OPEN FOR statement associates a cursor variable with a query, allocates database resources to process the query, identifies the result set, and positions the cursor before the first row of the result set. If the query has a FOR UPDATE clause, the OPEN FOR statement locks the rows of the result set.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

open_for_statement ::=

Description of the illustration open_for_statement.gif

using_clause ::=

Description of the illustration using_clause.gif

Semantics

open_for_statement

cursor_variable_name

The name of a cursor variable. If the cursor variable is the formal parameter of a subprogram, it must not have a return type. For information about cursor variables as subprogram parameters, see "Cursor Variables as Subprogram Parameters".

host_cursor_variable_name

The name of a cursor variable that was declared in a PL/SQL host environment and passed to PL/SQL as a bind argument. The data type of the cursor variable is compatible with the return type of any PL/SQL cursor variable. Do not put space between the colon (:) and host_cursor_variable_name.

select_statement

A SQL SELECT statement (not a PL/SQL SELECT INTO statement) or a string literal, string variable, or string expression of type CHAR, VARCHAR2, or CLOB that represents a SQL SELECT statement. Typically, the SELECT statement returns multiple rows.

See:

Oracle Database SQL Language Reference for SELECT statement syntax

dynamic_string

A SQL SELECT statement (not a PL/SQL SELECT INTO statement) or a string literal, string variable, or string expression of type CHAR, VARCHAR2, or CLOB that represents a SQL SELECT statement. Typically, the SELECT statement returns multiple rows.

See:

Oracle Database SQL Language Reference for SELECT statement syntax

using_clause

Specifies bind arguments.

Restrictions on using_clause 

• Use if and only if select_statement or dynamic_sql_stmt includes placeholders for bind arguments.

• If dynamic_sql_stmt has a RETURNING INTO clause, using_clause can contain only IN bind arguments. The bind arguments in the RETURNING INTO clause are OUT bind arguments by definition.

bind_argument

An expression whose value replaces its corresponding placeholder in select_statement or dynamic_string at run time. You must specify a bind_argument for every placeholder.

IN, OUT, IN OUT

Parameter modes of bind arguments. An IN bind argument passes its value to the select_statement or dynamic_string. An OUT bind argument stores a value that dynamic_string returns. An IN OUT bind argument passes its initial value to dynamic_string and stores a value that dynamic_string returns. The default parameter mode for bind_argument is IN.

Examples

• Example 6-26, "Fetching Data with Cursor Variables"

• Example 6-30, "Procedure to Open Cursor Variable for One Query"

• Example 6-31, "Procedure to Open Cursor Variable for Chosen Query"

• Example 6-32, "Procedure to Open Cursor Variable for Chosen Query"

• Example 7-4, "Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements"

Related Topics

In this chapter:

• "CLOSE Statement"

• "Cursor Variable Declaration"

• "EXECUTE IMMEDIATE Statement"

• "FETCH Statement"

• "OPEN Statement"

In other chapters:

• "Opening and Closing Cursor Variables"

• "OPEN FOR, FETCH, and CLOSE Statements"