Foreword

Change is inevitable. If you aren't prepared to accept and embrace it, you are bound to miss many opportunities. I use the above phrases to remind myself to be ready for that great new idea when it comes along. They came in especially handy when I first got to know Steven Feuerstein and his views on PL/SQL.

Back in September 1995, I was the development leader for a team in the Business Applications group at Symantec and we were gearing up for a new project, namely to build a front end to the customer information in Oracle's Financials package using the Forms component of Oracle's Developer/2000 toolset. We had never used Oracle Forms prior to Version 4.5 (probably a good thing) and this would be our largest effort involving PL/SQL-based code to date, so we had become intimately familiar overnight with the concept of "change." During the analysis phase of the project, I had encountered some of Steven's work in several articles, presentations, and his first book, Oracle PL/SQL Programming . After reading the book, it was clear that he had something different to say and that we might want to listen. His use of PL/SQL and packages in particular was like nothing I had seen before. I contacted Steven and asked him to come out and deliver his course as a kick-off to the development phase of the project.

After the entire development team had gone through the course and many of us had read a good part of his book, we became strongly motivated to streamline the decision-making process when coding, look for redundant code and put it into reusable modules, and build our code in layers by removing the implementation of a piece of functionality from its usage. Most importantly and more generally, we learned how to develop code in new ways. We knew we had a great opportunity with this project to apply Steven's lessons, producing an application and code base that would be flexible and maintainable.

The first step was to design the layers of code so that commonly used functions could be shared by any Symantec programmer building an application based on PL/SQL. Using packages, we took tasks like sequence generation and global variable management and put them in low-level modules. We put row date-time stamping functions in another layer, then moved on to create an application-specific layer and finally a form-specific layer. These layers were then used to construct highly robust and reusable templates for Oracle Forms development. Each layer used packages in a big way to isolate the functionality needed specifically within that layer. Fortunately, we had an early version of PL/Vision (Steven's library of PL/SQL code) and expanded on it to create many of the functions in the database layers.

Let me give you an example of how Steven's guidelines for improving the development process helped us out. I mentioned earlier that he talked to us about streamlining the decision-making process to reduce decision points and improve productivity. One of his specific recommendations in this vein was: always write explicit cursors when you need to execute a query in PL/SQL. Don't spend any time debating with yourself about whether to use a "quick and dirty" implicit or a programmatic explicit. Upon hearing this dogma, I was skeptical about the payback of the additional code required for the religious use of explicit cursors. This led Steven to reply: "Write the extra code for a week. By the end of the week, you won't even think about it any more."

He was absolutely right. The question of whether or not to use an explicit cursor has disappeared from our thought process. SELECT INTO is no longer used and OPEN-FETCH-CLOSE is now second nature. The result? Our code is more efficient, readable and consistent. Multiply the savings from that decision by 20 or 100 and it is easy to see how we were able to roll out our application in record time.

As the development of our application progressed, many shifts happened and we found it necessary to go back to completed modules and even change fundamentally the way a particular program was implemented. It was at this point that our layering efforts really paid off. We were usually able to make modifications (some of them rather significant) at the lower layers with no impact on the higher ones. We found with a big sigh of relief that it was even possible to add or modify key business rules without everyone having to change their own particular forms and high-level packages.

A few months into the development effort, our success showed that Oracle Forms was a viable tool and so a second, larger project was begun by another team. The task this time was to create an Oracle Developer/2000-based front end to the Order Entry module of Oracle's Financials application. We were determined that this new system would leverage many of the layers we had previously built and, sure enough, everything fell right into place. I have never seen an application move from specification to working code faster than Greyhound (the code name for this second system). Within 4 months, both of our ground-breaking applications were delivered to nearly 600 users in the smoothest roll-out any of us can remember.

In the projects I have just described, the majority of the PL/SQL code was written into the client-side of the equation, such as Oracle Forms PL/SQL libraries and forms. My next set of projects involve building browser-based applications for our corporate intranet: the kind of intranet which has our sales force directly updating core Financials datasets. These applications will now be written almost exclusively in PL/SQL packages residing in the server. Due to this fact, I expect that PL/Vision will become a core component of my toolbox. I have already developed a set of packages to manage data interactions with a Web browser client using PL/Vision and the techniques which have made it such an indispensable tool.

Maybe your users are different, but our internal customers expect to receive a new "shrink-wrapped" release of their applications packed with new features every few weeks (maybe Steven's next book could help us manage customers expectations). If it weren't for the things we have learned from Steven, directly from his code or as a result of his helping us think in new ways, we wouldn't be able to even imagine the maintainability we currently enjoy with our applications.

To help ensure that all our developers become proficient with PL/SQL as quickly as possible, every programmer in the Business Applications group is now given a copy of Oracle PL/SQL Programming as part of their new-hire kit. This second book will now be included in that kit and, for that matter, probably any other books Steven chooses to write in the future. I am constantly amazed at the different techniques and new possibilities we are able to learn about PL/SQL through Steven's efforts. I have also come to appreciate his ability to write about PL/SQL code construction in ways that keep me entertained and challenged, rather than nodding off late in the afternoon.

The bottom line is that Stevens "best practices" approach to PL/SQL has changed the way we at Symantec think about coding in PL/SQL. Through him, we have also begun to see the real power of Oracle's packages and how to use them in our everyday work. This "new order of things" has already paid off time and time again, not only for the developers in our organization, but for our customers. If you write PL/SQL code, I have no doubt that the ideas, software, and examples you find in this book will become a permanent part of your PL/SQL landscape.