|PL/SQL User's Guide and Reference
10g Release 1 (10.1)
Part Number B10807-01
OPEN-FOR-USING 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
Keyword and Parameter Description
A weakly typed cursor variable (one without a return type) previously declared within the current scope.
An expression whose value is passed to the dynamic
A string literal, variable, or expression that represents a multi-row
A cursor variable 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.
This optional clause specifies a list of bind arguments. At run time, bind arguments in the
USING clause replace corresponding placeholders in the dynamic
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.
The following example declares a cursor variable, then associates it with a dynamic
DECLARE TYPE EmpCurTyp IS REF CURSOR; -- define weak REF CURSOR type emp_cv EmpCurTyp; -- declare cursor variable my_ename VARCHAR2(15); my_sal NUMBER := 1000; BEGIN OPEN emp_cv FOR -- open cursor variable 'SELECT ename, sal FROM emp WHERE sal > :s' USING my_sal; ... END;