OPEN-FOR Statement

The 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 WHERE clauses.

Syntax

open for statement ::=

Description of the illustration open_for_statement.gif

using clause ::=

Description of open_for_using_statement.gif follows

Description of the illustration open_for_using_statement.gif

Keyword and Parameter Description

bind_argument

An expression whose value is passed to the dynamic SELECT statement.

cursor_variable_name

A cursor variable or parameter (without a return type) previously declared within the current scope.

host_cursor_variable_name

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.

select_statement

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.

USING clause

This optional clause specifies a list of bind arguments. At run time, bind arguments in the USING clause replace corresponding placeholders in the dynamic SELECT statement.

Usage Notes

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 */
BEGIN
  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;
END;

Other 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 IN 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: OPEN-FOR-USING, FETCH, and 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 (TRUE, FALSE, 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.

Examples

For examples, see the following:

Example 6-27, "Passing a REF CURSOR as a Parameter"
Example 6-29, "Stored Procedure to Open a Ref Cursor"
Example 6-30, "Stored Procedure to Open Ref Cursors with Different Queries"
Example 6-31, "Cursor Variable with Different Return Types"
Example 6-32, "Fetching from a Cursor Variable into a Record"
Example 6-33, "Fetching from a Cursor Variable into Collections"
Example 7-9, "Dynamic SQL Fetching into a Record"