Oracle Built-in Packages

Tips on Using Dynamic SQL

This section offers advice about how best to take advantage of dynamic SQL and the DBMS_SQL package. Following this section is a series of detailed examples of putting DBMS_SQL to use.

Some Restrictions

You can do a lot of awfully interesting stuff with DBMS_SQL, but some things are off-limits:

• You cannot manipulate cursor variables from within dynamic SQL. Cursor variables are a relatively new, advanced, and little-used feature of PL/SQL (see Chapter 6 of Oracle PL/SQL Programming for more information). But if you want to use them, you’ll have to do it in static PL/SQL code.

• Unless otherwise noted, DBMS_SQL does not support many of the new data structures in Oracle8. For example, you cannot bind an object or a nested table or a variable array.

Privileges and Execution Authority with DBMS_SQL

There are two basic rules to remember when working with DBMS_SQL:

• Stored programs execute under the privileges of the owner of that program. So if you parse and execute dynamic SQL from within a program, references to database objects in that SQL statement are resolved according to the schema of the program, not the schema of the person running the program.

• Roles are disabled when compiling and executing PL/SQL code. Privileges must be granted directly in order to be used with PL/SQL code. So when you execute dynamic SQL from within a PL/SQL program, you must have directly granted privileges to any database objects referenced in the dynamically constructed string. ...