Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page