13.46 OPEN FOR Statement
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
UPDATE clause, then the
FOR statement locks the rows of the result set.
Name of a cursor variable. If
cursor_variable is the formal parameter of a subprogram, then it must not have a return type. For information about cursor variables as subprogram parameters, see "Cursor Variables as Subprogram Parameters".
Name of a cursor variable that was declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. Do not put space between the colon (:) and
The data type of a host cursor variable is compatible with the return type of any PL/SQL cursor variable.
SELECT statement (not a PL/SQL
INTO statement). Typically,
select_statement returns multiple rows.
Oracle Database SQL Language Reference for
SELECT statement syntax
String literal, string variable, or string expression of the data type
CLOB, which represents a SQL
SELECT statement. Typically,
dynamic_statement represents a SQL
SELECT statement that returns multiple rows.
Specifies bind variables, using positional notation.
If you repeat placeholder names in
dynamic_sql_statement, be aware that the way placeholders are associated with bind variables depends on the kind of dynamic SQL statement. For details, see "Repeated Placeholder Names in Dynamic SQL Statements."
Restriction on using_clause
Use if and only if
dynamic_sql_stmt includes placeholders for bind variables.
IN, OUT, IN OUT
Parameter modes of bind variables. An
IN bind variable passes its value to the
OUT bind variable stores a value that
dynamic_string returns. An
OUT bind variable passes its initial value to
dynamic_string and stores a value that
dynamic_string returns. Default:
Expression whose value replaces its corresponding placeholder in
dynamic_string at run time. You must specify a
bind_argument for every placeholder.
Bind variables can be evaluated in any order. If a program determines order of evaluation, then at the point where the program does so, its behavior is undefined.
Restrictions on bind_argument
bind_argumentcannot be an associative array indexed by string.
bind_argumentcannot be the reserved word
To pass the value
NULLto the dynamic SQL statement, use an uninitialized variable where you want to use
NULL, as in Example 7-7.
Example 6-26, "Fetching Data with Cursor Variables"
Example 6-30, "Querying a Collection with Static SQL"
Example 6-31, "Procedure to Open Cursor Variable for One Query"
Example 6-32, "Opening Cursor Variable for Chosen Query (Same Return Type)"
Example 6-33, "Opening Cursor Variable for Chosen Query (Different Return Types)"
Example 7-8, "Native Dynamic SQL with OPEN FOR, FETCH, and CLOSE Statements"
Example 7-9, "Querying a Collection with Native Dynamic SQL"
In this chapter:
In other chapters: