Advanced Oracle PL/SQL Programming with Packages

19.3. The Dynamic Packages of PL/Vision

The builtin DBMS_SQL package supports all four methods of dynamic SQL, as well as dynamic PL/SQL code execution. It is a very powerful and useful tool that can in many ways transform the way in which you build applications, especially generic, reusable utilities, with PL/SQL.

There is, however, a tiny, little problem with DBMS_SQL: it is just too darn complicated. It consists of more than a dozen procedures and functions. These programs need to be used in a very particular sequence. Furthermore, depending on the method of dynamic SQL you wish to implement, you will use different combinations of those builtins. Finally, it just comes down to an awful lot of typing and know-how, even if you want to do something relatively simple. (See the sidebar for a general description of the flow of program calls for dynamic SQL. See Chapter 15, for a more complete description of DBMS_SQL.)

The result of this complexity is that relatively few developers take full advantage of all that DBMS_SQL has to offer. And since many of the actions required for dynamic SQL are the same regardless of the SQL statement, those individuals will be writing the same code over again.

What is wrong with this picture? Code redundancy is a maintenance nightmare. Requiring all developers to know the picayune details of technology like dynamic SQL is a productivity nightmare. Getting all of these versions of dynamic SQL to work is a code quality nightmare. Hey! Working with PL/SQL ...