|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 35 of 52
OPEN-FOR-USING statement associates a cursor variable with a multi-row 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 "Using the OPEN-FOR, FETCH, and CLOSE Statements".
This identifies a weakly typed cursor variable (one without a return type) previously declared within the current scope.
This is an expression whose value is passed to the dynamic
This is a string literal, variable, or expression that represents a multi-row
This identifies 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".
Any bind arguments in the query are evaluated only when the cursor variable is opened. So, 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.
In the following example, we declare a cursor variable, then associate it with a dynamic
SELECT statement that returns rows from the
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;