#

| Chapter 19 PLVdyn and PLVfk: Dynamic SQL and PL/SQL |

## 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, PLVvu: Viewing Source Code and Compile Errors, 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 should not resemble a Freddy Krueger sequel. There's got to be something we can do here.

The answer is simple, at least in concept: build a package. And that is what I did. In fact, I built three packages for dynamic SQL that make it easier to use the builtin DBMS_SQL package:

PLVdyn

  Gives a thorough layer of code built around the DBMS_SQL builtin package.

PLVdyn1

  Supports single bind variable dynamic SQL.

PLVfk

  Offers a generic utility to perform foreign key lookups for any table.

PLVdyn and PLVfk are covered in this chapter; PLVdyn1, which works in similar fashion to PLVdyn, is described on the companion disk.

19.2 Declarative Programming in PL/SQL |  | 19.4 PLVdyn: A Code Layer over DBMS_SQL

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.