Oracle PL/SQL for DBAs

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