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