[Chapter 19] PLVdyn and PLVfk: Dynamic SQL and PL/SQL Chapter 19 19. PLVdyn and PLVfk: Dynamic SQL and PL/SQLContents:About Plug-and-PlayDeclarative Programming in PL/SQLThe Dynamic Packages of PL/VisionPLVdyn: A Code Layer over DBMS_SQLDML OperationsPLVfk: Generic Foreign Key LookupsThis chapter describes several packages that provide a programmatic interface to the builtin DBMS_SQL package. These packages are the first in the "plug-and-play" category. Before plunging into a description of the specifics of the packages, I want to explain what I mean here by "plug-and-play" in PL/SQL code.19.1 About Plug-and-PlayPlug-and-play packages allow you to replace whole sections of code with programs from PL/Vision packages. You essentially "plug-in" PL/SQL code and immediately gain benefits in your application. The best example of a PL/Vision plug-and-play component is the PLVexc (PL/Vision EXCeption handling) package. It provides very high-level exception-handling programs so that individual developers can simply call a procedure that describes the desired action, such as "record and continue," and PLVexc figures out what to do. It makes use of PLVlog to automatically write errors to the log of your choice (database table, PL/SQL table, etc.). To give you a sense of plug-and-play in PL/SQL code, consider the following exception section. It has two different exception handlers: one for NO_DATA_FOUND and one for all other exceptions. When NO_DATA_FOUND is raised, I request that PLVexc display a message to the user, record the error, and then stop the program. When any other error occurs, I request that PLVexc record the error and then continue processing.EXCEPTION WHEN NO_DATA_FOUND THEN PLVexc.recNstop ('Company has not been located.'); WHEN OTHERS THEN PLVexc.recNgo; END;So PLVexc does a lot of work for me. What's new about that? You build a module encapsulating multiple actions and then use it over and over again. That's the central concept of modularization and black-boxing of logic. Why give it a fancy name like "plug-and-play"? Maybe it's just a difference of semantics. But maybe there's more to it. The package structure of PL/SQL offers new opportunities when it comes to modularizing code. You can think of a package as nothing more than a list of programs, a convenient way to collect together related modules. With this perspective, you will not break new ground with packages. If, on the other hand, you look upon the package as a self-contained environment or product or object, with its own internal data structures, its own set of rules, you will find that you can construct a whole -- the package -- that is considerably greater than the sum of its parts (the individual elements defined in the package). The PLVexc package certainly hides a lot of implementational complexity from its users. The real power of PLVexc is, however, reflected not so much in what or how much it hides. Rather, its strength resides more in what it lets you accomplish in your own programs -- and how you go about doing it.For detailed information about the PLVexc package, see Chapter 22, Exception Handling.V. Plug-and-Play Packages19.2 Declarative Programming in PL/SQL Copyright (c) 2000 O'Reilly & Associates. All rights reserved.