OPEN-FOR-USING Statement

The 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".

Syntax

Text description of the illustration open_for_using_statement.gif

Keyword and Parameter Description

cursor_variable_name

This identifies a weakly typed cursor variable (one without a return type) previously declared within the current scope.

bind_argument

This is an expression whose value is passed to the dynamic SELECT statement.

dynamic_string

This is a string literal, variable, or expression that represents a multi-row SELECT statement.

host_cursor_variable_name

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.

USING ...

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 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".

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.

Example

In the following example, we declare a cursor variable, then associate it with a dynamic SELECT statement that returns rows from the emp table:

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;

Related Topics

EXECUTE IMMEDIATE Statement