Oracle PL/SQL Programming, Third Edition

Some Words of Advice

Since 1995, when the first edition of this book was published, we have had the opportunity to train, assist, and work with tens of thousands of PL/SQL developers. In the process, we have learned an awful lot from our students and readers, and have also gained some insights into the way we all do our work in the world of PL/SQL. We hope that you will not find it too tiresome if we share some advice with you on how you can work more effectively with this powerful programming language.

Don’t Be in Such a Hurry!

We are almost always working under tight deadlines, or playing catch-up from one setback or another. We have no time to waste, and lots of code to write. So let’s get right to it—right?

Wrong. If we dive too quickly into the depths of code construction, slavishly converting requirements to hundreds, thousands, or even tens of thousands of lines of code, we will end up with a total mess that is almost impossible to debug and maintain. Don’t respond to looming deadlines with panic; you are more likely to meet those deadlines if you do some careful planning.

We strongly encourage you to resist these time pressures and make sure to do the following before you start a new application, or even a specific program in an application:

Construct test cases and test scripts before you write your code

You should determine how you want to verify a successful implementation before you write a single line of a program. By taking this approach (adopted from the Extreme Programming methodology), you are more likely to get the interface of your programs correct, and be able to thoroughly identify what it is your program needs to do.

Establish clear rules for how developers will write the SQL statements in the application

In general, we recommend that individual developers not write a whole lot of SQL. Instead, those single-row queries and inserts and updates should be “hidden” behind prebuilt and thoroughly tested procedures and functions (this is called data encapsulation). These programs can be optimized, tested, and maintained much more effectively than SQL statements (many of them redundant) scattered throughout your code.

Establish clear rules for how developers will handle exceptions in the application

Best of all, create a single error-handling package that hides all the details of how an error log is kept, determines how exceptions are raised and propagated up through nested blocks, avoids hardcoding of application-specific exceptions (those -20,NNN errors), and more. Make sure that all developers use this package and do not write their own complicated, time-consuming, and error-prone error-handling code.

Use “stepwise refinement” (a.k.a. top-down design) to limit the complexity of the requirements you must deal with at any given time

If you use this approach, you will find that the executable sections of your modules are shorter and easier to understand. This will make your code easier to maintain and enhance over time. Local or nested modules play a key role in following this design principle.

These are just a few of the important things to keep in mind before you start writing all that code. Just remember: in the world of software development, haste not only makes waste, it virtually guarantees a generous offering of bugs and lost weekends.

Don’t Be Afraid to Ask for Help

Chances are, if you are a software professional, you are a fairly smart individual. You studied hard, you honed your skills, and now you make a darn good living writing code. You can solve almost any problem you are handed, and that makes you proud.

Unfortunately, your success can also make you egotistical, arrogant, and reluctant to seek out help when you are stumped. This dynamic is one of the most dangerous and destructive aspects of software development.

Software is written by human beings; it is important, therefore, to recognize that human psychology plays a key role in software development. Here is one example:

Joe, the senior developer in a team of six, has a problem with his program. He studies it for hours, with increasing frustration, but cannot figure out the source of the bug. He wouldn’t think of asking any of his peers to help, because they all have less experience then he does. Finally, though, he is at wits’ end and “gives up.” Sighing, he picks up his phone and touches an extension: “Sandra, could you come over here and take a look at my program? I’ve got a problem I can’t figure out.” Sandra stops by and, with the quickest glance at Joe’s program, points out what should have been obvious to him long ago. Hurray! The program is fixed and Joe expresses gratitude, but in fact he is secretly embarrassed.

Thoughts like “Why didn’t I see that?” and “If I’d only spent another five minutes doing my own debugging I would have found it” run though Joe’s mind. This is understandable, but also very thick-headed. The bottom line is that we are often unable to identify our own problems because we are too close to our own code. Sometimes, all we need is a fresh perspective, the relatively objective view of someone with nothing at stake. It has nothing to do with seniority, expertise, or competence.

We strongly suggest that you establish the following guidelines in your organization:

Reward admissions of ignorance

Hiding what you don’t know about an application or its code is very dangerous. Develop a culture that welcomes questions and requests for help.

Ask for help

If you cannot figure out the source of a bug in 30 minutes, immediately ask for help. You might even set up a “buddy system,” so that everyone is assigned a person who is expected to be asked for assistance. Don’t let yourself (or others in your group) go for hours banging your head against the wall in a fruitless search for answers.

Set up a formal peer code review process

Don’t let any code go to QA or production without being read and critiqued (in a positive, constructive manner) by one or more other developers in your group.

Take a Creative, Even Radical Approach

We all tend to fall into ruts, in almost every aspect of our lives. People are creatures of habit: you learn to write code in one way; you assume certain limitations about a product; you turn aside possible solutions without serious examination because you just know it can’t be done. Developers become downright prejudiced about their own programs, and often not in positive ways. They are often overheard saying things like:

“It can’t run any faster than that; it’s a pig.”

“I can’t make it work the way the user wants; that’ll have to wait for the next version.”

“If I were using X or Y or Z product, it would be a breeze. But with this stuff, everything is a struggle.”

But the reality is that your program could almost always run a little faster. And the screen could in fact function just the way the user wants it to. And although each product has its limitations, strengths, and weaknesses, you should never have to wait for the next version. Isn’t it so much more satisfying to be able to tell your therapist that you tackled the problem head-on, accepted no excuses, and crafted a solution?

How do you do this? Break out of the confines of your hardened views and take a fresh look at the world (or maybe just your cubicle). Reassess the programming habits you’ve developed. Be creative—step away from the traditional methods, from the often limited and mechanical approaches constantly reinforced in our places of business.

Try something new: experiment with what may seem to be a radical departure from the norm. You will be surprised at how much you will learn and grow as a programmer and problem-solver. Over the years, I have surprised myself over and over with what is really achievable when I stopped saying “You can’t do that!” and instead simply nodded quietly and murmured “Now, if I do it this way . . . "