Advanced Oracle PL/SQL Programming with Packages

Advanced Oracle PL/SQL Programming with PackagesSearch this book
Previous: 19.2 Declarative Programming in PL/SQLChapter 19
PLVdyn and PLVfk: Dynamic SQL and PL/SQL
Next: 19.4 PLVdyn: A Code Layer over DBMS_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.


Previous: 19.2 Declarative Programming in PL/SQLAdvanced Oracle PL/SQL Programming with PackagesNext: 19.4 PLVdyn: A Code Layer over DBMS_SQL
19.2 Declarative Programming in PL/SQLBook Index19.4 PLVdyn: A Code Layer over DBMS_SQL

The Oracle Library Navigation

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

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference