4.1.1 The Benefits of PL/Vision

What can PL/Vision do for you? The possibilities are almost endless:

• Improve your productivity. PL/Vision goes a long way towards helping you avoid reinventing the wheel. Need to change a long string into a word-wrapped paragraph? Use the PLVprs.wra procedure. Want to display the contents of a PL/SQL table? Call the PLVtab.display procedure. Need to log activity to the database or maybe even write your information to a PL/SQL table? Call the PLVlog.put_line program. By using PL/Vision, you write much less code yourself, and instead spend your time deciding which prebuilt components of PL/Vision to plug into your own applications. You are able to focus much more of your effort on implementing the business rules for your systems.

• Decrease the number of bugs in your code and fix the bugs you do find more rapidly. Since you will be writing less code, you will minimize the opportunities for bugs to creep into your own programs. When you do have compile errors, you can call the PLVvu.err program to show you precisely where the error occurred in your program. Furthermore, PL/Vision packages offer many ways to trace the actions taken within those packages. When you need more information, you simply call the appropriate package toggle to turn on a trace and then run your test. When you are done testing and debugging, you turn off the trace mechanisms and put your application into production.

• Help you develop and enforce coding standards and best practices. You will do this in two ways: first, by using packages that explicitly support coding standards , such as PLVgen; second, by examining the code behind PL/Vision. This PL/SQL library has provided numerous opportunities for me to put my own best practices for packages into action. You will, no doubt, find occasional violations of my best practices, but by and large the code in PL/Vision should provide a wealth of ideas and examples for your own development.

• Increase the percentage of reusable code in your applications. The more you leverage PL/Vision, the fewer new programs you have to write yourself. And this advantage doesn't just accrue to individual developers. You can use PL/Vision across multiple applications -- it can be part of a truly enterprise-wide object and module library.

• Demonstrate how to modularize and build layers. I don't want you to simply use PL/Vision. I want you to learn how and why I built PL/Vision so that you can accomplish the same kind of development yourself. We all need to be fanatically devoted to modularizing code for maximum reusability. We all need to become sensitive to identifying program functionality that should be broken out into different layers. To some extent, you can develop such sensitivity only by practicing the craft of software construction. But you can also examine closely the work of others and learn from their example (both the good and the bad).

• Inspire you to be creative, to take risks in your coding. I have found that the real joy of programming is to be found in trying out new ways of doing things. When you stretch boundaries -- whether they are the boundaries of your own experience or those of the documented features of a language -- you make discoveries. And when those discoveries turn out to be productive, you create new things.

4.1.2 The Origins of PL/Vision

PL/Vision has both top-down and bottom-up origins. Many of the pieces of PL/Vision were pulled together after the fact; I would come up with an interesting package, then draw it into the embrace of PL/Vision. After incorporating it I would reexamine other packages in PL/Vision to see how I could leverage this latest addition. I consider that the bottom-up approach.

PL/Vision as a coherent library of interlocking packages first sprang out of a recognition of the need to break up a single large program into multiple layers of code. In the fall of 1995 I became obsessed with the idea of writing a program that would "pretty-print" or reformat PL/SQL source code to follow my suggested coding style. It would read the code from the ALL_SOURCE data dictionary view and perform such tasks as upper-case all keywords and apply consistent indentation and line breaks to the code. I worked feverishly after hours on this project for a week and found myself with a working prototype: the psformat procedure. It was an exciting week for me. I could feed psformat (which ran to almost 1000 lines of text) the code for a procedure stuffed into one long, unformatted string and retrieve from it a nicely formatted PL/SQL program unit.

I was careful not to write a parser for the PL/SQL language. I didn't think such a step was necessary to handle my limited scope and I sure didn't want to spend the time required for such a task. Yet I found (as many of you would probably anticipate) that psformat didn't handle all the nuances of PL/SQL code correctly. So I would dive back in and tinker with it just a little bit more so that it would understand this or that element of the language.

Two weeks later, I had completely reimplemented psformat three times. With each implementation I came closer to handling the conversion and formatting of a wide range of PL/SQL program syntax. At the same time, my one big program grew that much bigger and more convoluted. I was just so taken up in implementing my obsession that I did not feel that I had the time to modularize my code. And each time I came that much closer to cobbling together a parser for PL/SQL .

After round three and too little sleep, I began to realize that I would probably never meet my objectives taking this piecemeal approach. Painfully and reluctantly, I gave up on this program (as you will see, PL/Vision does not provide a program to automatically indent and line-break your code). I did not, however, abandon all the code I had developed. Instead, I set my sights on a more limited and achievable goal: a program that would convert the case of source code to follow the UPPER-lower method. I would leave the format of the code as is, but change all keywords to upper-case and all application-specific identifiers to lower-case.

In the process of enhancing psformat , I had constructed a table that contained the keywords for the PL/SQL language and information about how those keywords affected the format of the source code (this is now the PLV_token table). I also recognized that I wanted to be able to read the original code from any number of different sources and write the converted code to any number of different targets.

At the same time that I shifted my expectations and goal, I forced myself to take the time to break up my monster procedure and employ top-down design to straighten out my logic. Thus was PL/Vision born. Directly out of this process, I created a single PLVtext package to manipulate PL/SQL source text. This single package eventually transformed itself into PLVio, PLVobj, and PLVfile to handle very generically a variety of source and target repositories for PL/SQL source code. I separated out many string-parsing tasks from psformat into PLVprs, which over time broadened into PLVprs, PLVlex, PLVtkn, and, finally, PLVprsps. When all of my layering and partitioning was done, I got back to building the PLVcase package to perform the actual case conversion. My monstrous single procedure became a very small, relatively simple package.

As I broke out these different aspects of PL/SQL functionality, I began to see the outlines of a whole substantially larger than the sum of its parts: a very generic and highly reusable toolbox that could be used in any PL/SQL -based development environment. From that point on, I was on the lookout for any utility or package I had built or would build that could add to the scope and functionality of PL/Vision.

Throughout this development process, I found myself learning more and more about what was really involved in building packages and leveraging reusable code. My conception of a set of best practices for packages also crystallized.

My desire to share what I had learned and built grew with the number of packages in PL/Vision. Finally, I realized that the best way to make the most of my knowledge was to write a book centered around the code and lessons of PL/Vision. I hope that you will find the software and the accompanying text useful.