Oracle PL/SQL Programming, 5th Edition

When to Use DBMS_SQL

Native dynamic SQL should be your first choice (over DBMS_SQL) to satisfy dynamic SQL requirements in your PL/SQL programs for the following reasons:

• NDS is much easier to write; you need less code, and the code you write is more intuitive, leading to many fewer bugs. The code is also much easier to maintain.

• NDS works with all SQL datatypes, including user-defined objects and collection types (associative arrays, nested tables, and VARRAYs). DBMS_SQL works only with Oracle7 Database-compatible datatypes.

There are, however, situations when you will want or need to use DBMS_SQL. The following sections describe these situations.

Parse Very Long Strings

Through Oracle Database 10g, EXECUTE IMMEDIATE executes the contents of a VARCHAR2 string, with a maximum length of 32K. (What if your SQL statement exceeds that length? While that scenario is unlikely for SQL statements you write yourself, generated dynamic SQL statements based on tables with many columns (you can now have up to 1,000 columns in a table) could easily exceed that limit. DBMS_SQL to the rescue!

Note

With Oracle Database 11g, EXECUTE IMMEDIATE can execute either a VARCHAR2 string or a CLOB. A new overloading of DBMS_SQL.PARSE also accepts a CLOB for parsing.

Use a special overloading of DBMS_SQL.PARSE to parse arbitrarily long SQL and PL/SQL statements by passing a collection to the built-in that contains the full text of the dynamic statement. The collection must be of type DBMS_SQL.VARCHAR2S (maximum bytes ...