Preface
I love getting started on new projects (and I include working on new editions of existing books in that general category). It is the perfect opportunity to say to myself: “I am going to get it right this time!”
That fantasy usually persists only for days (or maybe weeks) into a project before it fades, but in the case of the second edition of Oracle PL/SQL Best Practices, I managed to live out my fantasy all the way through. You are holding the result in your hands, and I hope you enjoy reading and learning from it as much as I enjoyed writing it.
Here’s how I managed this remarkable feat: I took a vow to not let best practices get boring.
Now, don’t get me wrong. I liked the first edition of this book, and so did its many readers. Yet in hindsight, I feel as if I took a right turn when I should have kept going straight. My first book, Oracle PL/SQL Programming, has been extremely popular over the years, and many people have told me how much they like the sense of humor, the anecdotes, and the many detailed examples.
Several years after the publication of Oracle PL/SQL Programming, I wrote Oracle PL/SQL Best Practices. And somehow, for reasons I cannot recall, I managed to make this book a somewhat preachy and rigidly structured text. Luckily for me, developers seem to like lots of structure and don’t mind too much being preached at by people they trust!
But as I considered revamping this book for its second edition, I found myself thinking: best practices are really important, but that doesn’t mean they have to be serious—they can be fun and entertaining (as much for me to write as for you to read)!
So that’s what I did: I had fun writing this book. I sacrificed some of the rigidity of structure, emphasized practicality over theoretical usefulness, and generally came down off my perch (don’t worry—there are still more than enough rants and soapboxing!). In this second edition, I’ve tried to make the discussion a lot more interesting by sharing many of my ideas about best practices through stories about the successes and failures of the employees of the fictitious company, My Flimsy Excuse, Inc., and the adventures of its development team (the members of the team are described later in the Preface). Of course, I have also updated the text to keep pace with Oracle Corporation’s implementation of new PL/SQL features, including those in Oracle Database 11g.
Why Best Practices?
My intention with this book is to provide you with a set of best practices—guidelines for building programs and applications—that will improve (and, I hope, transform) the PL/SQL code you write. Some of these best practices are high-level recommendations for how to approach designing and writing your overall applications. Other best practices are much more oriented to day-to-day programming practice. In most of my recommendations, I include references to code or tools that you can download that will make it easier for you to apply these recommendations.
Chapter 1, The Big Picture, covers the high-level recommendations for writing high-quality code. The remaining chapters of the book explore how to apply specific guidance to the everyday realities of software development. Before we delve into these specific recommendations, though, I would like to take advantage of this Preface to offer some thoughts on the role of software developers—and the nature of our jobs—in the big, wide world “out there” beyond our code.
Best Practices and the Real World
Clearly, software developers want to (and should) pay attention to best practices so that they will be able to create more successful applications. I also believe, however, that the responsibility of developers goes well beyond the specific projects on which they are working.
After all, you are one of the people who make software possible, who therefore make computer systems like the Internet possible. That is an awesome amount of power, but as Spider Man’s uncle tells us, “With great power comes great responsibility.”
From this perspective, it is very important to understand that you are not just a cog in the corporate wheel. When you are told to write a program, you should engage fully with that request. You should make sure not only that you understand in detail what it is the user wants you to write, but also that you appreciate how this program will meet its business objectives.
As a programmer, you are trained to think logically about the challenges in front of you. This rational thought process is not something to be taken for granted. Many other humans have never received such training and don’t know how to reason their way through complex and thorny problems. Given this background, you can often help your users more clearly understand their own roles and their own challenges.
Your responsibility also extends beyond business objectives. You have an ethical responsibility to make sure that the software you write will not cause harm. Let’s face it: executives of companies cannot always be trusted to do the right thing for their customers, for the company and its employees, and for society at large. Let’s not forget the terrible lessons learned from Enron, Arthur Andersen, Union Carbide, Halliburton, and so many others. If you write software that enables executives in your firm to break the law or simply take advantage of those less fortunate, then you are complicit in that unethical behavior.
Of course, most of us will never be placed in such an ethical quandary. Instead, our main challenge will be to remember how much humanity depends on software, and how much we can all be hurt by buggy software, software that is not secure, and software that is simply badly designed—programs that waste the time of users and greatly increase their stress levels.
They Call This “Work”?
Can you really call what we do work? Most people on this earth work very hard in exchange for a paycheck. They drive buses, clean sewers, work in factories, pick vegetables and fruit under a blazing sun, teach children, take care of sick people—hard work that often takes a toll on their health and well-being. We, on the other hand . . . what do we do? Here are a variety of ways to characterize our “work”:
- Programming as poetry
We sit around in nice, air-conditioned offices or cubicles. We think about things and write them down (or type them). And for this we get a paycheck. It’s like getting paid to write poetry (and some of us actually do feel that our programs are a form of poetry).
- Programming as Sudoku
We play logical puzzles, similar in nature and often form to Sudoku or Mastermind, all day long. Incredible! Seriously, think about it: when your manager asks you to implement a program, it may initially sound boring and therefore like work. Here’s an example: suppose you are told to write a program to calculate the mortgage payments for a customer. OK, you can be bored with it, or you can in your mind “translate” the specification for that program into the logic puzzle that it really is. The solution is the working program. The logical steps you have to go through represent the algorithm you will need to write.
- Programmer as mediation
Software generally aims to capture a small slice of the real world within cyberspace. Cyberspace is an artificial environment of our own making that is powered by computers. Computers are dumb machines that perform computations very rapidly. Because computers are dumb, humans must communicate with them using very rigid syntax and commands, following patterns that match up with the computer. Most people don’t know the syntax or are not very good at thinking in these ways (procedural, object-oriented, symbolic logic, etc.). So we, the programmers, act as intermediaries between “normal people” and dumb computers.
Software is a responsibility, but it can also be a joy. So the next time you find yourself complaining about how dull your job is, or how you hate Cubicle Land and wish you could see out a window, please try to keep in perspective just how wonderful our lives really are!
And then express your gratitude for the opportunity to live a “life of the mind” by writing the best possible code and testing it thoroughly. Your users will thank you, and you will be so much more satisfied with life inside that cubicle.
The Cast of Characters
Software is still, for the most part, written by humans. So who are the humans who write the software I describe and critique in this book? Let’s meet the cast of characters who inhabit the second edition of Oracle PL/SQL Best Practices. They work for a company called My Flimsy Excuse,[1] Inc. (MFE), and their web site is myflimsyexcuse.com. The mission statement of My Flimsy Excuse is simple and powerful:
Provide a wide array of excuses (flimsy and otherwise) to people in need of a way to explain away questionable behavior.
The MFE founders love technology and don’t want to have any flimsy excuses for failure, so they have chosen Oracle as their database technology. They have several different development teams working on various aspects of the web-deployed business plan. The team that has graciously volunteered to share its experiences with my readers is made up of the following individuals:
- Sunita
The team leader, a very smart woman with a fast, razor-sharp mind. She is always busy, always constructive, and somewhat intimidating. She was a programmer in years past, mostly trained in Fortran and then some C+. She isn’t sure if that makes her a better manager or a bigger danger to the team, but she still likes to get her hands “dirty” now and then, writing code.
- Delaware
A classic anarchistic, anti-standards, big ego kind of programmer. He has a hard time learning from anyone else, and writes code that is hard to understand . . . but he is very productive and very bright. If you need a job done overnight (literally), Delaware is the guy to do it. He claims to read up on all the latest features of PL/SQL, but he generally programs in a rut—relying on techniques learned years past in Oracle7. He keeps his thinning hair neatly trimmed, with just a hint of a comb-over, and favors three-piece suits from the Men’s Wearhouse.
- Lizbeth
The anchor of the team. She used to write in Cobol and is continually shocked at the lack of strong process in the PL/SQL world. She can’t understand why programmers today make fun of the Cobol programmers of the past. Didn’t they write the software that made the first phase of the Information Revolution a broad success? Lizbeth is methodical and careful, but not, on the whole, the best problem solver—she too easily falls into the trap of seeing things only from her own perspective.
- Jasper
The junior member of the team. He is new to PL/SQL and new to MFE. Jasper is eager to learn from anyone and everyone and has nice thick skin, but he is not very creative (i.e., is not ready to take risks in his code). He always wears jeans, preferably of the distressed variety, complemented by polo shirts with the logos of animals on them. Lizbeth thinks of him as the son she never had, and Delaware treats him like the mascot of the team.
As you can see, each person has her or his own strengths and weaknesses. We will learn from both of these characteristics as we make our way through this book.
Structure of This Book
As I mentioned earlier, I decided to loosen up a bit structure-wise in the second edition. That does not mean, however, that you are about to start reading a chaotic and hard-to-navigate book. This section summarizes the organization.
Oracle PL/SQL Best Practices is composed of nine chapters and two appendixes. Each chapter contains a set of best practices for a particular area of functionality in the PL/SQL language or for broad programming principles (the latter are concentrated mostly in Chapter 1).
Best practices are used to overcome problems in our everyday programming life and to encourage the writing of high-quality code. To reinforce this purpose, most of the best practices in the book are presented first as a problem in the MFE environment: Sunita’s team or a member of it confronts a challenge or has followed a “worst practice” into a very uncomfortable spot. We will then see how the MFE development team applies the best practice at hand to meet the challenge or repair the nasty code.
In many of the best practices, I also offer references to files available for download from the book’s web site, as well as to publicly available tools (freeware, open source, and commercial) that will help you apply the best practices in a practical and efficient manner. You will find a comprehensive list of all such references in Appendix B.
Most best practices in this book are introduced by two titles: the first title is often a bit tongue-in-cheek (less than totally serious) and the second title is a more serious recommendation.
Chapter 1, offers advice about how to improve the overall process by which you write code. The emphasis is on the application as a whole and the processes used to write that application, rather than on concrete suggestions for specific aspects of programming in PL/SQL. Please don’t skip Chapter 1: if you do, it will be difficult to fully utilize the best practices in the rest of this book.
Chapter 2, is all about, well, standards: setting rules for how you and everyone on your team should write code. These standards range from naming conventions to standards templates from which code should be built. By the time you have finished reading this chapter, I hope that you will have overcome your ingrained resistance to standards, and instead see that rules free up more of your time to write the more interesting parts of your application.
Chapter 3, explores the steps that should take place after you get your program to compile, because, let’s face it, that’s just the beginning. You will learn about testing, tracing, and debugging—and the differences among these activities.
Chapter 4, takes a close look at how you can best declare and manage data structures and variables within your PL/SQL programs. PL/SQL is a strongly-typed language, which means that you need to make lots of decisions yourself in this area.
Chapter 5, is a “back to basics” chapter that talks about the best way to write IF statements, loops, and the new (in Oracle Database 11g) CONTINUE statement. Sure, these aren’t terribly complicated constructs, but there are still right and wrong ways to work with them.
Chapter 6, covers exception management (“stuff going wrong”). We will explore defining standards for raising, handling, logging, and communicating errors back to the user. It is impossible to build a high-quality application with inconsistent error management, so be sure to read this chapter before you start your next project!
Chapter 7, focuses on a crucial aspect of PL/SQL development: how you should write the SQL statements in your program to improve programmer productivity, increase performance, and make your code more maintainable. My feeling is that most PL/SQL developers are totally addicted to writing SQL—and that addiction leads to some of the biggest mistakes in how we write our code.
Chapter 8, offers advice on specifying parameters and building maintainable, readable, reusable procedures and functions—the program units that contain your business logic. It also presents recommendations for packages, the building blocks of well-designed PL/SQL-based applications, and triggers, which allow you to associate business rules with specific database objects.
Chapter 9, provides guidelines for improving the performance of your PL/SQL programs. These best practices range from techniques that will have an amazing impact on execution time (reducing program elapsed time from hours or days to mere minutes) to more granular recommendations whose impact will vary depending on the type of code you are writing.
Appendix A, compiles the best practices across all the chapters into a concise resource. Once you have studied the individual best practices, you can use this appendix as a checklist, to be reviewed both before you begin coding a new application and while you are implementing your programs.
Appendix B, offers descriptions of the files (examples and reusable code) that are referenced in the book, plus a list of the most useful books and online resources to help you take full advantage of the PL/SQL language.
How to Use This Book
My primary goal in writing this book was to create a resource that would make a concrete, noticeable difference in the quality of the PL/SQL code you write. To accomplish this, the book needs to be useful and usable not just for general study, but also for day-to-day, program-to-program tasks. It also needs to be concise and to the point. A 1,000-page text on best practices would be overwhelming, intimidating, and hard to use. The result is this relatively brief (I consider any publication under 300 pages a major personal accomplishment!) and entertaining book.
You can certainly read and apply the best practices in this book selectively. The best way to leverage best practices, however, is to start from a solid understanding of both your challenges and the high-level flows needed to break out of the current programming ruts in which you may find yourself today. So start with Chapter 1 (what a concept!) and see what you think of my “big picture” ideas. If you find yourself disagreeing with any of these ideas, think through what bothers you about them and what you would do instead to achieve the same results. If you like the ideas, spend some time planning how to apply them within your team. The higher level the best practice is, the more challenging it can be to get it started and ingrained in a group of developers.
Once you have absorbed (or rejected) the advice in Chapter 1, it will be easier to take advantage of the more specific best practices in the other eight chapters. I suggest that you proceed from start to end (don’t panic—it’s not that long a book!) with a light reading of each chapter, saving a full exploration of all the details until later on. Try to picture the best practices as a whole, reinforcing the following themes:
I want to write code that I—and others—can easily understand and change as needed.
The world is terribly complex, so I should strive to keep my code simple. I can then meet that complexity through carefully designed interaction among elements of my code.
You will then be well positioned to delve more deeply into the chapter or specific best practice that seems most critical to you at that moment.
Another very handy way to take advantage of this book is to use the code that is available for download. See the upcoming section "About the Code" for a discussion of the software that will help you bring your best practices to life.
Conventions Used in This Book
The following typographical conventions are used in this book:
- Italic
Used for file and directory names, for URLs, for emphasis, and for introducing a new term.
Constant widthUsed for code examples.
Constant width italicIndicates text that should be replaced with user-supplied values.
Constant width boldIndicates user input in examples showing an interaction. Also, in some examples, highlights the statements being discussed.
- UPPERCASE
Generally indicates PL/SQL keywords, names of built-in packages, etc.
- lowercase
Generally indicates identifiers (e.g., names of variables, procedures, functions, etc.).
Tip
Indicates a tip, suggestion, or general note. For example, I’ll tell you if a certain setting is version-specific.
Warning
Indicates a warning or caution. For example, I’ll tell you if a certain setting has some kind of negative impact on the system.
About the Code
The best way to learn how to write good code is by analyzing and following examples. Almost every best practice offered in this book includes a code example, both in the text and in downloadable form. I will be keeping the code up to date at my PL/SQL portal at:
| http://www.ToadWorld.com/SF |
There you will also find training materials and additional code downloads. You can also obtain the example code, along with additional information on the book, through the O’Reilly Oracle PL/SQL Best Practices web page at:
| http://www.oreilly.com/catalog/9780596514105 |
Whenever possible, the code I provide for the book can be used to generate best-practice-based code and as prebuilt, generalized components in your applications, code that you can use without having to make any modifications.
The code examples offer programs that you can use to both generate and directly implement those best practices. In some cases, the programs are rather simple “prototypes”; they work as advertised, but you will probably want to make some changes before you put them into production applications.
And you should most certainly test every single program you use from Oracle PL/SQL Best Practices! I have run some tests, of course, and my wonderful technical reviewers have also exercised the code. In the end, however, if the code goes into your application, you are responsible for making sure that it meets your needs.
Comments and Questions
I have tested and verified the information in this book and in the source code to the best of my ability, but if you find an error and want to comment on the book or the code, please notify me. By postal mail, you can contact me through O’Reilly at:
| O’Reilly Media, Inc. |
| 1005 Gravenstein Highway |
| Sebastopol, CA 95472 |
| 800-998-9938 (in the United States or Canada) |
| 707-829-0515 (international/local) |
| 707-829-0104 (fax) |
You can also send messages electronically. To be put on the mailing list or request a catalog, send email to:
| info@oreilly.com |
To ask technical questions or comment on the book, send email to:
| bookquestions@oreilly.com |
For more information about books, Resource Centers, and the O’Reilly Network, see the O’Reilly web site:
| http://www.oreilly.com |
As mentioned earlier, the book’s web site is:
| http://www.oreilly.com/catalog/9780596514105 |
Safari Books Online
When you see a Safari Books Online icon on the cover of your favorite technology book, it means the book is available online through the O’Reilly Network Safari Bookshelf.
Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top technical books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.
Acknowledgments
The second edition of Oracle PL/SQL Best Practices is a much improved text, largely as a result of the assistance of many fine Oracle technologists. Any errors, however, are entirely my fault and responsibility.
A special thanks to John Beresniewicz for his detailed technical review and the contribution of a fine summary of Design by Contract in Chapter 1. I hope that readers will go from the brief presentation in that chapter to the expanded description of DbC on the book’s web site. I believe that using this powerful programming style will greatly strengthen your code.
My heartfelt thanks to Bryn Llewellyn, PL/SQL Product Manager at Oracle Corporation, for deepening my knowledge of PL/SQL and helping me crystallize and focus my best practices.
Technical reviewers for this second edition provided many corrections and ideas that improved the text. My deepest gratitude to Dick Bolz, Rick Greenwald, Darryl Hurley, Dwayne King, Giovanni Jaramillo, Arup Nanda, Chris Rimmer, and Bert Scalzo.
And from O’Reilly Media, thanks to my editor Deborah Russell. On the first edition, she got me off the dime and helped me turn the book around in record time; while we developed this second edition over a longer period, it was, once again, a real pleasure working with you, Debby! Thanks as well to Rachel Monaghan, the production editor; Rob Romano, who developed the figures; and Angela Howard, who wrote the index.
The second edition couldn’t have happened without the first edition, which never would have hit the shelves without assistance from John Beresniewicz, Rohan Bishop, Dick Bolz, Dan Clamage, Bill Caulkins, Dan Condon-Jones, Fawwad-uz-Zafar Siddiqi, Gerard Hartgers, Edwin van Hattem, Dwayne King, Darryl Hurley, Giovanni Jaramillo, Vadim Loevski, Pavel Luzanov, Matthew MacFarland, Jeffrey Meens, James “Padders” Padfield, Rakesh Patel, Bill Pribyl, Andre Vergison (the brains behind PL/Formatter), and Solomon Yakobson.
Last and most definitely not least, I thank my wife, Veva, and my two boys, Chris and Eli, for tolerating all my time in front of a computer and for giving me endless and most excellent reasons to escape from it.
[1] * A “flimsy excuse” is an explanation for an action that is not very convincing and is easily exposed as a rationalization or cover-up for the real intention of the action.
Get Oracle PL/SQL Best Practices, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
