The OPEN FOR Statement
The OPEN FOR statement was not introduced into PL/SQL for NDS; it was first offered in Oracle7 to support cursor variables. Now it is deployed in an especially elegant fashion to implement multiple-row dynamic queries. With DBMS_SQL, you go through a painful series of steps to implement multi-row queries: parse, bind, define each column individually, execute, fetch, extract each column value individually. That’s a lot of code to write!
For native dynamic SQL, Oracle took an existing feature and syntax—that of cursor variables—and extended it in a very natural way to support dynamic SQL. Let’s look at the syntax of the OPEN FOR statement:
OPEN {cursor_variable | :host_cursor_variable} FOR SQL_string
[USING bind_argument[, bind_argument]...];where:
- cursor_variable
Weakly typed cursor variable
- :host_cursor_variable
Cursor variable declared in a PL/SQL host environment such as an Oracle Call Interface (OCI) program
- SQL_string
Contains the SELECT statement to be executed dynamically
- USING clause
Follows the same rules as in the EXECUTE IMMEDIATE statement
Tip
If you are using Oracle9i Database Release 2 or Oracle Database 10g, you can use EXECUTE IMMEDIATE with BULK COLLECT to retrieve multiple rows with a dynamic query. This approach requires much less code and can improve the performance of your query operation.
Following is an example that demonstrates the declaration of a weak REF CURSOR type, a cursor variable based on that type, and the opening of a dynamic ...