OPEN-FOR statement executes the query associated with a cursor variable. It allocates database resources to process the query and identifies the result set -- the rows that meet the query conditions. The cursor variable is positioned before the first row in the result set. For more information, see "Using Cursor Variables (REF CURSORs)".
With the optional
USING clause, the statement associates a cursor variable with a query, executes the query, identifies the result set, positions the cursor before the first row in the result set, then zeroes the rows-processed count kept by
%ROWCOUNT. For more information, see "Building a Dynamic Query with Dynamic SQL". Because this statement can use bind variables to make the SQL processing more efficient, use the
OPEN-FOR-USING statement when building a query where you know the
WHERE clauses in advance. Use the
OPEN-FOR statement when you need the flexibility to process a dynamic query with an unknown number of
open for statement ::=
using clause ::=
An expression whose value is passed to the dynamic
A cursor variable or parameter (without a return type) previously declared within the current scope.
A cursor variable previously declared in a PL/SQL host environment and passed to PL/SQL as a bind variable. The datatype of the host cursor variable is compatible with the return type of any PL/SQL cursor variable. Host variables must be prefixed with a colon.
A query associated with
cursor_variable, which returns a set of values. The query can reference bind variables and PL/SQL variables, parameters, and functions. The syntax of
select_statement is similar to the syntax for
select_into_statement defined in "SELECT INTO Statement", except that the cursor
select_statement cannot have an
INTO clause. The length of the dynamic string cannot exceed 32767K.
You can declare a cursor variable in a PL/SQL host environment such as an OCI or Pro*C program. To open the host cursor variable, you can pass it as a bind variable to an anonymous PL/SQL block. You can reduce network traffic by grouping
OPEN-FOR statements. For example, the following PL/SQL block opens five cursor variables in a single round-trip:
/* anonymous PL/SQL block in host environment */
OPEN :emp_cv FOR SELECT * FROM employees;
OPEN :dept_cv FOR SELECT * FROM departments;
OPEN :grade_cv FOR SELECT * FROM salgrade;
OPEN :pay_cv FOR SELECT * FROM payroll;
OPEN :ins_cv FOR SELECT * FROM insurance;
OPEN-FOR statements can open the same cursor variable for different queries. You do not need to close a cursor variable before reopening it. When you reopen a cursor variable for a different query, the previous query is lost.
Unlike cursors, cursor variables do not take parameters. Instead, you can pass whole queries (not just parameters) to a cursor variable. Although a PL/SQL stored procedure or function can open a cursor variable and pass it back to a calling subprogram, the calling and called subprograms must be in the same instance. You cannot pass or return cursor variables to procedures and functions called through database links. When you declare a cursor variable as the formal parameter of a subprogram that opens the cursor variable, you must specify the
OUT mode. That way, the subprogram can pass an open cursor back to the caller.
You use three statements to process a dynamic multi-row query:
CLOSE. First, you
OPEN a cursor variable
FOR a multi-row query. Then, you
FETCH rows from the result set. When all the rows are processed, you
CLOSE the cursor variable.
The dynamic string can contain any multi-row
SELECT statement (without the terminator). The string can also contain placeholders for bind arguments. However, you cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.
Every placeholder in the dynamic string must be associated with a bind argument in the
USING clause. Numeric, character, and string literals are allowed in the
USING clause, but Boolean literals (
NULL) are not. To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
Any bind arguments in the query are evaluated only when the cursor variable is opened. To fetch from the cursor using different bind values, you must reopen the cursor variable with the bind arguments set to their new values.
Dynamic SQL supports all the SQL datatypes. For example, bind arguments can be collections,
LOBs, instances of an object type, and refs. As a rule, dynamic SQL does not support PL/SQL-specific types. For instance, bind arguments cannot be
BOOLEANs or index-by tables.
For examples, see the following: