By Steven Feuerstein
Price: $29.99 USD
£18.50 GBP

Cover | Table of Contents | Colophon

Table of Contents

Chapter 1: The Big Picture

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Are you happy with the quality of the code you write? Probably not, or you wouldn't be reading this book! This doesn't mean that you are a "bad" programmer. It means only that you feel that your code has room for improvement—and I'm sure that's true for every single programmer among us.

I believe that you can dramatically improve the quality of your code by following programming best practices. Two very interesting concepts are implicit in that term:

• It is possible to talk about a "best" way of writing code, which implies, conversely, that there is a worst or at least suboptimal way to write code.
• These "best" ways can be organized into "practices," formalized processes for writing high-quality software.

After having written software (and books about writing software) for almost 30 years, I am firmly convinced that these two concepts are both valid and fundamentally important, and that you can't have one without the other.

Humanity survived its first worldwide software crisis on January 1, 2000—but at a cost of several hundred billion dollars. Users of software, unfortunately, continue to experience localized software crises on a daily basis, as they struggle with poorly written applications that are a direct consequence of the QUAD "methodology" (QUick And Dirty).

Software has the potential to dramatically improve the quality of life of billions of human beings. It can—with the help of robotics—automate tedious and dangerous processes. It can make information and services more widely accessible. It can, should, and must play a role in halting the degradation of our environment. In short, software has an enormous potential, but that potential will never be realized unless we can find a way to substantially improve the quality of the code we write.

This book has a very simple but ambitious purpose: to help Oracle developers and development teams transform the way they write PL/SQL-based applications. To achieve this transformation in your programming life, you will need both to rethink fundamental aspects of application design and construction, and to change day-to-day programming habits.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Successful Applications Are Never an Accident

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If you have the privilege of buying a new automobile and rolling it off the dealer's lot, you don't say to yourself:

Well, every 50 miles or so, one of my wheels might fall off. The odometer sometimes goes backward, and, oh look! My windows don't quite close. But that's all right, because the dealer said that I'll be getting version 2 in roughly six months. I can't wait!

Sounds silly, doesn't it? Yet for people who buy and use software, it has become the norm to think in precisely these terms. Now, if an automobile manufacturer really did deliver cars that were as buggy as many of the applications let loose upon the world, that manufacturer would likely be out of business very soon. While it is certainly possible that a software company will go belly up if its code is too buggy, for the most part our employers keep on going (and we keep on programming), issuing new versions that fix some bugs and introduce others.

Why is there such a difference in the way people view, use, and tolerate cars versus software? I think there are three basic reasons:

• Cyberspace, the world of software, is a world of our creation. We determine what is possible and not possible within that world. This total control gives us the ability to "upgrade" that world, fixing problems, adding possibilities, and adding constraints, with relative ease. You just can't do that with "real" products.
• Cyberspace is, at its core, an attempt by software developers to simulate a small slice of the real world, and then automate some processes to help users accomplish things in that real world. Because the world "out there" is so incredibly complex, writing software is a very hard thing to do, and even harder to do well.
• Human society (at least those societies in which the Industrial and Information Revolutions have had their way with us) is today unimaginable without computers and the software that makes computers useful. Software permeates almost every aspect of our lives. And that means that for the most part our users are a "captive audience": they have to use what we give them, no matter how faulty.

I am going to assume that you are reading this book because you would like to pick up some pointers about how to improve the quality of the PL/SQL code that you write. You would like to do so in order to produce applications that are more successful. Perhaps it would be helpful, therefore, to remind ourselves of what it means for an application to be successful.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Best Practices for Successful Applications

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Best practices are the guidelines you should follow to achieve a "best" or successful application—one that meets the criteria listed in the previous sections. "Best practices" is certainly an overused term, but it is also clear enough in intent to be useful.

When programmers follow best practices, they have made a decision to work against their "quick and dirty" tendency and to consciously, with intention and purpose, seek to transform the way they write their code. Without that focus, without a deliberate act on the part of developers (and their managers as well), there is a very low likelihood that their applications will be successful.

Best practices also must go beyond (and deeper than) words on a page. For best practices to be successfully applied, they must be combined with tools and scripts to make them practical.

Best practices should operate at two levels:

Big picture

A high-level workflow that provides an overall guide to writing code. "Big picture" best practices for constructing applications and applying fundamental principles are usually decided on before a project starts. Best practices in this category set standards for all of the code written in an application.

Day-to-day

Concrete recommendations for specific aspects of code construction that are applied in each new program as it is written and maintained.

Both types of best practices are important. The following sections focus on the key "big picture" best practices that will set the stage for the day-to-day recommendations you will find in the rest of the book.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Software is like ballet: choreograph the moves or end up with a mess.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: In software, the ends (production code) are inseparable from the means (the build process).

It is back in 2004. Sunita has just been promoted to development manager at My Flimsy Excuse, Inc., and has been given responsibility for both a newly assembled team (Delaware, Lizbeth, and Jasper), and a critical new application development project. She is feeling a little bit overwhelmed and intimidated. Delaware has been around forever and is famous (infamous?) for both his brilliant coding and his brittle personality. Lizbeth has more quietly, but just as firmly, established herself within the company as a valuable resource; she serves on several standards committees and sends out a monthly "What's New in the MFE Family" newsletter. Who is Sunita, compared to them?

She calls the first meeting of the team and begins to lay out her ideas for implementing the project. Delaware, attired in a very retro double-breasted suit, immediately breaks in and explains in no uncertain terms why her approach will fail. "We are all very experienced," he says sternly. "Why not leverage that experience and free us up to do what we do best?" The others nod, and Sunita feels she has little choice but to agree.

So all three developers dive in with enthusiasm, applying to their part of the application their own individual approaches to writing code. Sunita checks in regularly, but she feels more like an outsider than a manager at this stage. She reassures herself that she has a team of professionals building the application; they don't need lots of direction.

Three months into the project, problems start popping up. Delaware's code needs to talk to Lizbeth's code, but the way they have each designed their interfaces makes such communication very difficult. And not long after that, during a rare code review session (insisted upon by Sunita), they discover that they have each built their own error-management code, writing to different tables and storing different information.

But it is too late to turn back now, so the team struggles on, adding new layers of code to achieve some level of interoperability and consistency. The application grows more and more complex; deadlines are missed; performance is very uneven, with some programs running very slowly even with average quantities of data; tensions rise between developers who used to get along just fine. Finally, though, they tell Sunita that they have spent the last few days testing the code and fixing bugs; the application is now ready for QA. But the QA group finds that the application has so many low-level bugs that it can't get even halfway through an acceptance test.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Deferred satisfaction is a required emotion for best practices.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

If we give in to our "base impulse" and simply start writing code, implementing interesting algorithms, we will always struggle to apply best practices. We need to start off on the right foot by preparing for the coding phase. I believe there are four key steps to preparation, shown in .

Figure : Four steps of preparing an application

Step 1. Validate program requirements

Sometimes your users give you very little documentation, and other times they overwhelm you with nice-looking documents that contain graphics, charts, and lots and lots of text. In both cases (and everything in between), do not assume that what the users say is right or complete. As I pointed out earlier, your users may not have developed the same ability to think clearly and logically that programmers have. So help them! Use your logical skills to help users identify illogical ideas or gaps in their requirements.

Before you start writing any code, make sure that both you and your users have a clear understanding of what is needed (and why). So ask lots of questions, shine light in the dark corners, and challenge users to make sure that they have thought through all the requirements. Remember too that what users ask for is not always the easiest way to solve a problem. Don't assume that users have already considered other approaches—and I am not talking only about programming algorithms; I am talking about business processes as well.

Step 2. Implement just the header of the program

OK, at this point let's assume that you are reasonably confident that your users know what they need and have clearly communicated those requirements to you. Now can you write your program? Yes, but just a little bit of it: only the header of the program and enough to get your program to compile.

At this stage, it's time to think not about how the program will be implemented (oh, but I really like to come up with clever algorithms!), but instead about:

What is a good name for the program?

If your name does not accurately represent the purpose of the program, it will mislead anyone who reads your code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Contracts work for the real world; why not software, too?

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: What is never discussed is never agreed upon.

Sunita comes late to the weekly team meeting one Friday morning to find Delaware and Lizbeth engaged in a heated discussion over Delaware's new excuse_in_use utility function. Lizbeth says:

The excuse_in_use Boolean function you wrote sometimes returns null and that makes it a pain to use. I always have to wrap calls to it with NVL and decide—yet again—that NULL=FALSE. Having to worry about null values in computations can really cause trouble in the form of hard-to-catch bugs. Can't excuse_in_use just return TRUE or FALSE, period?

Delaware counters:

Well, it wouldn't be such a problem if callers like your customer_support package could be counted on not to pass null excuses into the function. But how am I supposed to know what you want when you hand me a null excuse to test? Since we've never discussed it, I figure the best policy for null is to hand out what is handed in, which is to say exactly no information. The program is more robust because it handles more input combinations without failing. It's called defensive programming.

Sunita breaks in to put a stop to the bickering:

You are both arguing plenty, but communicating very little. Delaware, defensive programming is a fine approach when the potential callers of a module are unknown or otherwise unpredictable, but Lizbeth sits in the cube right next to you! Maybe we should defend less and cooperate more by coming up with some clear agreements about how our code should behave.

They look over Delaware's code, which tracks excuses using a package-private collection indexed by the excuse strings:

PACKAGE BODY excuse_tracker
IS
   TYPE used_aat IS TABLE OF BOOLEAN INDEX BY excuse_excuse_t;
   g_excuses_used   used_aat;

   FUNCTION excuse_in_use (excuse_in IN excuse_excuse_t)
     RETURN BOOLEAN
   IS
   BEGIN
      IF excuse_in IS NULL
      THEN
         RETURN null;
      ELSE
         RETURN g_excuses_used.EXISTS (excuse_in);
      END IF;
   END excuse_in_use;

   ...other programs...
END excuse_tracker;

Sunita goes on:

In this case, Lizbeth does not want Delaware's excuse_in_use function to ever return a null value instead of a proper Boolean, and similarly, Delaware does not want Lizbeth's program to call his function with a null value for its input argument. So let's just satisfy both of them by requiring Delaware's function to only return TRUE or FALSE, but only under the obligation that Lizbeth's code does not pass in a null value. In fact, this is a very good rule in general for all Boolean functions, and we should probably always follow it.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Don't act like a bird: admit weakness and ignorance.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Steven is a hypocritical programmer.

I spend a lot of my time in public talking about best practices. In other words, I stand up in front of other developers and act "holier than thou," offering advice and admonitions along the lines of "Do this, don't do that, and certainly never do the other thing."

Occasionally, I am honest enough to point out that I do not always follow all my best practices. And students in my classes are, not infrequently, delighted to point out violations of best practices in my own code as I show it up on the big screen.

As the former owner of an African Grey parrot, I have learned quite a bit about the way birds behave, and have been struck by parallels with the ways programmers go about their business. Most telling for me is how sick birds instinctively hide their symptoms. When a bird gets sick, it tries to avoid letting anyone or anything know there is a problem. Why is this? In the wild, sick birds attract the attention of predators (sick birds are easier to catch). The flock maintains its strength by saying "adios" to its weakest members, and so sick birds become lunch for predators.

The parallel to programmers? We don't like to admit that we don't know everything, because we sense that admitting ignorance will diminish our position in the "flock" (development team). So we hide our ignorance, and inevitably push that ignorance (and its associated bugs) into our code.

I do think that lots of my code is at least reasonably well written. Sometimes, though, the way that I ignore my own recommendations is so over the top and painful that my hypocrisy is brought to the fore. Along these lines, I feel compelled to make a confession. Back in June 2007, I was in Europe presenting the Quest Code Tester product to developers, DBAs, and managers in Paris, Brussels, and Maidenhead (U.K.). When not in the public eye, I kept myself very busy debugging some of Quest Code Tester's backend code (on which I am the lead developer) as well as working on the second edition of Oracle PL/SQL Best Practices for O'Reilly (the book you are now reading).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Five heads are better than one.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Sunita spent six months developing comprehensive coding standards for her group.

Huh? That's a problem? Well, not in and of itself. The problem with what Sunita did is that she produced a rather thick document (50 pages of top-notch ideas and advice), made copies, and distributed it to everyone on the team. She then assumed that the team members would diligently follow the coding standards, so she turned her attention to other pressing matters. Months went by, and Lizbeth, Delaware, and Jasper wrote lots of code in their separate cubicles, pushing hard to meet deadlines.

A few weeks before it is time to deliver the application to users, Sunita finds that she has a couple of days free from the burdens of management tasks (paperwork, for the most part). So she decides to get in touch with the code base, both to refresh her familiarity with PL/SQL and to get a comfort level about the application-specific algorithms.

She opens up a package body and starts reading through the code. As she does so, she finds herself bothered by ... something ... she can't quite put her finger on it, and then she realizes what it is: this code is not following the standards she defined months before! Names of variables, program comment header blocks, exception handling: none of it looks anything like the standard.

"Well," she thinks, "that package was written by Delaware." (She has always had her doubts about whether he would accept her ideas.) "Let's check Lizbeth's code."

So she opens another file and is immediately pleased. Lizbeth's code looks completely different from Delaware's. Finally, someone is following the standards! But on closer review, her sunny feelings turn to despair. It's true that Lizbeth's code is noticeably different from Delaware's, but she isn't following the group standard, either! Instead, she has her own naming conventions and her own approach to exception handling.

Sunita calls a meeting and asks everyone what the heck is going on. There are lots of downcast eyes and clearing of throats. "Well?" Sunita demands, "What did you do with those coding standards I put together?" It turns out that those 50-page tomes were placed in desk drawers and never looked at again.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Don't write code that a machine could write for you instead.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Jasper is starting to feel more like a robot than a human being.

Lately, it seems to Jasper that he isn't using a single ounce of creativity in his brain. Instead, he finds himself relying on copy-paste-and-change to write his programs. The latest frustration in this area arises when Sunita announces that she wants him to write functions to return single rows of data from key tables for a given primary key value. He writes this one first:

FUNCTION excuse_for (id_in IN mfe_excuses.id%TYPE)
   RETURN mfe_excuses%ROWTYPE
IS
   retval  mfe_excuses%ROWTYPE;
BEGIN
   SELECT * INTO retval
     FROM mfe_excuses
    WHERE id = id_in;
   RETURN retval;
END excuse_for;

Then he has to do the same thing for customers, so he copies and pastes, changes the names of the tables, and ends up with this:

FUNCTION customer_for (id_in IN mfe_customers.id%TYPE)
   RETURN mfe_customers%ROWTYPE
IS
   retval  mfe_customers%ROWTYPE;
BEGIN
   SELECT * INTO retval
     FROM mfe_customers
    WHERE id = id_in;
   RETURN retval;
END excuse_for;

Unfortunately, he now has another 25 tables for which he needs to build these functions. How boring! But he does it, with every fiber of his soul rebelling, and finally he is done.

Then he shows the code to Sunita, who says, "What if the query raises TOO_MANY_ROWS? Don't we want to log that error? It certainly would be good to know about any violations of our primary keys!"

Holding back a scream, Jasper forces himself to nod, writes the exception section, and then does a copy-and-paste 25 more times. And all he can think about as he is doing this is, "What a terrible way to spend my time!"

Solution: If you can recognize a pattern in what you are writing, generate code from that pattern.

Life is short—and way too much of it is consumed by time spent in front of a computer screen, moving digits with varying accuracy over the keyboard. It seems to me that we should be aggressive about finding ways to build our applications with an absolute minimum of time and effort while still producing quality goods. A key component of such a strategy is code generation: rather than write the code yourself, you let some other piece of software write the code for you.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

We need more than brains to write software.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Science fiction is an awful lot of fun to read in books and watch in the movies. It's amazing what computers and cyborgs and so on can accomplish when they are not constrained by the economic and technical realities encountered on Planet Earth.

Well, we not only live in the real world, we write software that attempts to mimic within cyberspace a small fraction of that real world. And since the real world is always changing, there is always great pressure on us and our applications to "keep up." That results in great job security, but also tremendous challenges.

One fundamental reality we must keep in mind as we explore best practices for writing software is that our programs are written almost entirely by us, human beings. Sure, we can and should take advantage of the code-generation tools discussed earlier, but for the most part, software development doesn't happen without our sitting in front of a screen and typing. The ramifications of this simple, undeniable fact are far-reaching:

• The software we write and how we go about writing it are affected greatly by the physiology (hard-wiring) of our brains, and the psychology of humans as we interact with one another and our environment.
• Our brains can handle only so much complexity at a time (though we certainly can train our gray matter to juggle larger and larger volumes of data and structures). We need to find ways to organize our activity so that we don't get overwhelmed, confused, and lost.
• We are lucky that we are able to make a living off the product of our brains. Our brains need our host bodies in order to function, so we need to make sure we take care of those hosts. Humans didn't evolve in order to sit in front of a monitor 6, 8, or 10 hours a day.

Here are my top recommendations for taking care of your host body. You can think of these as best practices for you, rather than your code:

Drink lots of water

You probably hear this advice a lot. Without a doubt, you will generally be much healthier if you drink more water and less coffee, Coke, and other caffeinated, sugared products. But specifically when it comes to brain work, if you get dehydrated, it's like a car engine without enough oil. Your brain gets sluggish and dull. Here is my concrete suggestion: the next time you come back from a heavy lunch and find yourself nodding off at your desk, do

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 2: Real Developers Follow Standards

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

We software developers are a very privileged bunch. We don't have to work in dangerous environments, and our jobs aren't physically taxing (though carpal tunnel syndrome is always a threat). We are paid to think about things, and then to write down our thoughts in the form of code. This code is then used and maintained by others, sometimes for decades.

Given this situation, I believe we all have a responsibility to write code that can be easily understood and maintained (and, c'mon, let's admit our secret desires, admired) by the developers who follow in our footsteps. Look at this way: if you have a child, she might grow up to be a programmer. She might even put in time at one of your previous employers. She might actually end up having to maintain code that you wrote.

The choice is yours: avoid mortifying embarrassment either by never putting your name in your code, or by writing code that you would be proud to show to, share with, and inflict on your own flesh and blood.

Sadly, programmers are also often a fairly arrogant and anarchistic bunch. Part of the reason for this is that those people "out there" (nonprogrammers) tend to labor under the misconception that you have to be really smart to write software. Now, I am sure that all of my readers are really, really intelligent, don't get me wrong. But you don't need to be brilliant to write software. You need to be good at acting as a translator between normal people (users) and machines (computers). You need to be good at thinking like a machine, putting together thoughts (commands) in logical sequences that those nonsentient computers can follow.

You know how it is with your own propaganda: after a while, you believe it yourself. And so we programmers have come to think that we are really smart, that no one can teach us anything, that we each individually know the best way to write code, format code, and structure code. The end result is often that even in a small team of developers, the code for an application will be all over the map, with everyone following different naming conventions, different coding formats, and different ways of documenting workarounds.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Best Practices for Developing and Using Standards

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

This section describes best practices for using standards in your code and determining ways to enforce the use of standards by your development team.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

It's a free country; I don't have to use carriage returns in my code.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Delaware writes code that no one else can read.

For a while after Sunita took over My Flimsy Excuse's development team, she thought seriously about firing Delaware. The guy was obviously brilliant in so many ways, but some forms of brilliance are best experienced in isolation—or anywhere else but where you are.

For example: Delaware adhered to the most awful formatting style Sunita had ever seen. He refused to use the carriage return key. He also favored uppercase. As a result, his programs looked like this:

CREATE OR REPLACE PACKAGE OVERDUE_PKG IS PROCEDURE SET_DAILY_FINE (FINE_IN
IN NUMBER); FUNCTION DAILY_FINE RETURN NUMBER; FUNCTION DAYS_OVERDUE (ISBN_IN IN
BOOK.ISBN%TYPE)RETURN INTEGER; FUNCTION FINE_AMOUNT (ISBN_IN IN BOOK.ISBN%TYPE)
RETURN INTEGER; END OVERDUE_PKG;

Now, it really did seem that Delaware could follow and even maintain his code when it was in this format. He always got the job done. But certainly no one else could bear to lay eyes upon his work. And what would happen when Delaware moved on (assuming that anyone else would have him)?

Solution: Use the built-in functionality of your IDE to automatically format your code.

Rather than fire Delaware, Sunita calls everyone together one afternoon to discuss the merits of a common coding format for the team. Unsurprisingly, all the members of the team have their own personal favorites: Jasper likes to uppercase only PL/SQL keywords; Lizbeth insists on a four-space indentation; and Delaware refuses even to participate. Rather than throw her hands up in frustration, Sunita gathers the best ideas and then spends some time experimenting with the IDE that everyone on the team uses.

Almost immediately, she notices that the tool has its own automatic formatting feature. She checks with the team and soon realizes that no one uses—or even knows about—the feature. Gadzooks! In short order, she tweaks the formatting options to match what she considers to be a reasonable compromise for the team standard. She then issues the following instructions:

• We have automatic formatting available. You can pick whatever style you want for your own use as you develop your code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Too much freedom is a very bad thing.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Jasper's eagerness to help is overwhelmed by his hurry to get it done.

Jasper has always loved public libraries. In a world in which everything seems to be commercialized and privatized, the library stands as a beacon of free knowledge, free speech, and equal access. Jasper visits his local library each week and has gotten to know the head librarian (Marguerite) well. During one of his visits, Marguerite asks him if he can help with their computer system.

To Jasper's delight, the system is based on Oracle and PL/SQL. He agrees to help, and she asks him to build a program to display all the books borrowed on a certain date. He writes this procedure:

PROCEDURE showBorrowedBooks (date_borrowed IN DATE)
IS
   date_returned CONSTANT DATE := SYSDATE;
   minDaysBorrowed PLS_NTEGER := 10;

   TYPE bookBorrowed IS RECORD (
      dateBorrowed DATE,
      daysBorrowed PLS_INTEGER,
      isbn         book.isbn%TYPE,
      dateDue      DATE);

   bb bookBorrowed;

   CURSOR c IS
      SELECT * FROM borrowed_book
       WHERE returned = 'N';
BEGIN
   IF dateborrowed < date_returned
   THEN
      FOR rec IN c
      LOOP
         bb:= rec;

         IF bb.daysBorrowed > minDaysBorrowed
         THEN
            DBMS_OUTPUT.PUT_LINE (bb.isbn);
         END IF;
      END LOOP;
   END IF;
END showBorrowedBooks;

Putting aside the issue of what it is supposed to do, this is a very hard program to read! It's formatted nicely enough, but the ways in which Jasper has named and formatted identifiers are inconsistent and downright puzzling; for example:

• Some identifier names use an underscore ("_") as a break between words in the name.
• Other names rely on "camel notation," with the first letter of a new word capitalized. This style is very common in Java, but is quite dangerous in PL/SQL, since PL/SQL is not a case-sensitive language. Most PL/SQL auto-formatters will obliterate camel notation, decreasing readability.
• Even if you use camel notation in your source code for the name of a program, that name will be automatically uppercased in the data dictionary. In other words, you would not find a program named showBorrowedBooks in the data dictionary. Instead, you would find SHOWBORROWEDBOOKS.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Good names lead to good code.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Badly formed or inaccurate names can greatly reduce usability of programs.

Delaware builds a new package with two subprograms. Here are their headers:

PACKAGE misc_stuff
IS
   FUNCTION calculate_totals (...) RETURN NUMBER;
   PROCEDURE favorite_excuses (...);
END misc_stuff;

He then writes a block of code using their programs and it goes something like this:

BEGIN
   IF misc_stuff.calculate_totals (...) > 10000
   THEN
      misc_stuff.favorite_excuses (...);
   END IF;
END;

Putting aside the lack of arguments for the moment, don't you find yourself stumbling over this code as you read it? "If calculate totals is greater than 10,000, then favorite excuses" . . . huh? We can pretty well assume that the calculate_totals function calculates and returns the total of . . . something. Yet that is only an assumption. And what's going on with those favorite excuses? Are we displaying them, deleting them, assigning them, or what?

Solution: Construct subprogram names so they reflect both what they are and what they do.

Suppose Delaware had defined the package specification as follows:

PACKAGE misc_stuff
IS
   FUNCTION total_excuse_revenue (...) RETURN NUMBER;
   PROCEDURE show_favorite_excuses (...);
END misc_stuff;

Then his block of code would look like this:

BEGIN
   IF misc_stuff.total_excuse_revenue (...) > 10000
   THEN
      show_favorite_excuses (...);
   END IF;
END;

I wouldn't expect you to have any questions about what this program is supposed to be doing. The code explains itself.

Delaware achieved this effect by following these guidelines:

A procedure joins together (and runs) a series of logically related executable statements

The name of the procedure should reflect what those statements do, and should be in the form of a verb phrase, as in verb_subject. "favorite_excuses," on the other hand, doesn't really tell anyone what is going on with those excuses.

A function executes one or more statements with the express intent of returning a value

The name of a function should describe what is being returned and be in the form of a noun phrase, as in

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Put your checklists into your code.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Checklists on paper rarely translate into changes in the way we write our code.

When Lizbeth first arrived at My Flimsy Excuse, the management team realized that she was the most experienced developer on board. So they asked her to develop some standards. Well, Lizbeth did more than that! She came up with a series of simple, easy-to-read checklists that would help the developers write high-quality code.

Lizbeth delivered her materials to management, complete with instructions on how and when they should be applied. They made lots of copies and distributed them to all of the developers. A few weeks later, when Lizbeth's development team held its monthly code review, Lizbeth was shocked to see that none of her checklists seem to have been followed. She asked around and found that everyone liked her checklists, but after reading them, they put them into their desk drawers, more or less forgot the ideas in the checklists, and went on coding as they had before.

Solution: Make your checklists active and directly involved in development.

Standards are a great thing, but standards that are available only in the form of a document are almost never followed. Who can remember what the document suggests? Who has time to go back and look at it?

Well, Lizbeth is a stubborn human being, so she doesn't give up right away. Instead, she realizes that to get a programmer to follow a standard, she needs to:

• Make it easier to follow the standard than to not follow it (who can argue with that?).
• Not tell anyone that they are following a standard (thus avoiding that ingrained, anarchistic rejection).

Lizbeth decides to take another approach. She has been using Toad for Oracle, and she remembers reading about the product's Code Templates feature. So she builds some templates that incorporate a number of items on her checklist, such as:

• Isolate initialization and cleanup code for packages and subprograms in their own nested blocks.
• Make sure to execute cleanup in the execution and exception sections.
• Make sure that all exception handlers rely on the predefined error API.
• Use a standard header comment block to describe the program.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Who needs comments? My code is self-documenting!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: One person's clarity is another person's bewilderment.

Jasper is having a good time! He has just learned about collections, collections of records, and collections of collections, and he is enjoying himself tremendously using these complex data structures. He is deep into his program, fully conceptualizing the structures—what they mean, how they relate. And while he is in "the zone," he writes code like this without giving it a second thought:

IF master_list(l_curr_index).properties_flag.field1 = 'N' THEN

And at that moment, he understands exactly what it means. A few months later, however, a user reports a bug in the application, and it is traced back to the program containing this line of code. Jasper is on holiday, so Lizbeth is assigned the job of fixing this bug.

When she looks at the code, she gets really angry. What does it mean? How is she supposed to have any idea how to fix the program when she can't even figure out what is going on? Well, there is nothing to do but push through her frustration. But she sends off a somewhat nasty note to Jasper telling him, "You should put in some comments if you are going to write such cryptic stuff, Jasper!"

Jasper comes back to work and is surprised by the email. He remembers with joy the clarity of the moment when he wrote that code. What was hard to understand? To his utter dismay, however, when he opens up the file and looks at the code himself, he realized that even he can't understand the intent of that line of code . . . how embarrassing!

Solution: Sometimes you really do need to add comments.

Well, Jasper knows he has lots to learn, and he has clearly made a mistake here. A comment is needed. And so he changes the code to this:

/* If the first field of the properties record is N... */
IF master_list(l_curr_index).properties_flag.field1 = 'N' THEN

Unfortunately, when he shows this to Lizbeth, she gets even more irritated. "Jasper, this doesn't help. It doesn't tell me anything beyond what the code already says!" Jasper sighs, "So I should explain what my code is doing, right? OK, how about this?"

/* If the customer is not eligible for a discount... */
IF master_list(l_curr_index).properties_flag.field1 = 'N' THEN

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Chapter 3: Life After Compilation

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

It's nothing short of amazing how excited we programmers can get when our newly built program simply compiles for the first time. What a great achievement! It makes us feel as if we're almost done implementing that program.

Well, sure, a clean compile is an important milestone (one we are sure to experience again and again, as we apply fixes to our code). But it is also just the beginning of a long journey that will someday lead to our program being placed into production, which is sort of like being voted into the Hall of Fame. Real users will now be using our code. We rock!

You've probably heard sayings about how the journey is more important than the destination. That's sort of true with software, but also false. The only reason to embark on the journey is to produce that production-ready piece of code that normal human beings will use (after all, if you write a program and no one ever runs that program, does the program really exist?).

The journey from a clean compile to a fully tested and optimized program is important—don't get me wrong. If you follow the wrong path, you will never get to your destination. And if you take lots of shortcuts on the journey, the end point (the completed program) will be so deeply flawed that it will be rejected or, perhaps worse, detested by your users.

This chapter looks at several key phases in the journey from compilation to production. In particular, I focus on several different activities that every programmer engages in, to widely varying extents, so he or she can reach that golden moment: testing, tracing (also known as instrumentation), and debugging. Another key aspect of post-compilation activity is reviewing code, which I discuss in .

Before diving into the details, let's explore some high-level thoughts concerning the different terms related to identifying and fixing bugs in your code:

Testing

Test your code to identify bugs that occur under a certain set of circumstances (a "test case" with specific inputs to arguments in the program).

Tracing

Turn on tracing to obtain what is usually a substantial amount of "raw data" about what the program did as it ran.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Testing, Tracing, and Debugging

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before diving into the details, let's explore some high-level thoughts concerning the different terms related to identifying and fixing bugs in your code:

Testing

Test your code to identify bugs that occur under a certain set of circumstances (a "test case" with specific inputs to arguments in the program).

Tracing

Turn on tracing to obtain what is usually a substantial amount of "raw data" about what the program did as it ran.

Debugging

Use your debugger to isolate the specific lines of code that caused the bug (and that correspond to the flow indicated by the trace data).

Then use your Integrated Development Environment (IDE) to fix the bug, and start the cycle all over again!

When you test a program, you run test code that exercises the program (usually, but not always, to verify the functionality of the program—in other words, does it do X?). That test code then tells you whether or not the test succeeded. Alternatively, you will have to manually go through the results of the test code and deduce for yourself whether it worked. Generally, the more you can automate the testing process the better. Automation saves you time, and most programmers are rarely able to "set aside" much time for testing.

A test identifies a problem in your code, but it usually doesn't give you a whole lot more information than that ("You passed in 16 and should have gotten back 12; instead, you got 10."). You then need to figure out which lines of code are causing that problem. Both tracing and debugging can help you do that.

When you trace execution of your program, you run that program and as it is running, you record or build a trace of information about what is happening inside the program.

In Oracle PL/SQL, the most common (and also the crudest) type of tracing is a call to the DBMS_OUTPUT.PUT_LINE procedure, which sends information to the screen after the program has finished running. A more elaborate built-in tracing mechanism is the DBMS_APPLICATION_INFO package.

It is not at all rare to come across PL/SQL applications that are full of (littered with?) calls to DBMS_OUTPUT.PUT_LINE. I generally avoid ever making a direct call to this built-in. For example, in the Quest Code Tester backend, I use a tracing utility, adapted from the Quest CodeGen Utility qd_runtime package, to allow me to flexibly turn tracing on and off, and write information to the qu_log table. From there, I can extract the data to display on the screen, fill up a file, and so on. Here is a simple example from the Quest Code Tester backend:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Best Practices for Testing, Tracing, and Debugging

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the following sections, you will find descriptions of the best practices that I recommend you follow after your programs compile. I discuss both processes and tools for testing, tracing, and debugging.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Thanks, but no thanks, to DBMS_OUTPUT.PUT_LINE!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: DBMS_OUTPUT.PUT_LINE is inadequate for tracing.

As I noted earlier, DBMS_OUTPUT.PUT_LINE is the most common tracing mechanism employed by PL/SQL developers—and it is one of the worst from which you can choose (or make yourself).

I am very glad that Oracle provided DBMS_OUTPUT in version 2 of PL/SQL. Before that, it was difficult to debug code, because there was no easy way to trace program execution to the screen. However, the implementation of DBMS_OUTPUT leaves much to be desired. Here are my complaints:

It's a productivity disaster

You have to type 20 characters (and a typo or two) just to ask PL/SQL to show you something. Hey, what can I say? Life is short and every character counts.

The overloading is inadequate

You can pass only single strings, dates, or numbers. You can't pass it a Boolean value, nor can you pass it multiple values to be displayed (without doing the concatenation yourself).

The string length is limited

Prior to Oracle Database 10g Release 2, if you try to display a string with more than 255 characters, you get one of two errors: ORA-20000 (a.k.a. ORU-10028 line length overflow) or ORA-06502 (numeric or value error). I don't know about yours, but a whole lot of my strings are longer than 255 bytes.

The number of lines is limited

For releases before Oracle Database 10g Release 2, your program can display a maximum of 1 million lines—and it can be lots less if you forget to specify a high number in your SET SERVEROUTPUT command in SQL*Plus (resulting in an out-of-buffer error). Starting with Oracle Database 10g Release 2, you can now request that the buffer size be unlimited.

There is no incremental feedback

You don't see anything on your screen until your PL/SQL program has finished executing—whether that takes five minutes or five hours.

Solution: Build a layer of code over DBMS_OUTPUT.PUT_LINE.

When you are faced with a utility such as DBMS_OUTPUT that is simultaneously necessary and faulty, you should say out loud (it will make you feel better):

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Assume the worst, and you will never be disappointed.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: We live in the moment, and don't think about what it will take to maintain our code.

For the past few years, Sunita has overseen the development of an application made up of hundreds of tables and even more packages. It has grown like a rather ugly pearl, with layer upon layer of new code, patches to existing code, etc. Of course, almost all of this structure is hidden from user view. A user can simply type a value into a field and press the Submit button; a second later, some information is displayed on the screen. But that field is like the tip of an iceberg floating above the water line. By pressing Submit, the user has actually triggered the execution of thousands of lines of code, performing all sorts of really complicated activities.

And today, a user typed in something new and different, pressed Submit, and received an unhandled exception. She calls Support; Support notifies Sunita; then Sunita asks Lizbeth to track down the source of the problem. It is a new one, and they really have no idea why or where it is occurring. Lizbeth then takes these steps:

1. She analyzes the code to determine which programs are being called.
2. She compiles those programs in debug mode.
3. She runs the code (emulating user input) and uses the source code debugger to step through the application line by line to identify the problem.

And four hours later, there she is: still stepping through the code line by line. Now, Lizbeth is a disciplined and determined programmer, so she shows no signs of weariness, but it sure is taking a lot longer to track down the problem than Sunita had originally thought. Why is this taking so long?

General Solution: Build instrumentation (tracing) into your application from the start.

The problem is that Lizbeth is relying on the wrong technique (debugging) to track down the source of the issue, or she has started that debugging process too early.

Just about any substantial production application will execute thousands and thousands of lines of code. An interactive debugger that lets you set breakpoints and step through code is a fantastic tool (and is discussed later in this chapter). Debuggers pretty much assume, however, that you have localized the source of your problem and are now ready to zoom in. Since Lizbeth is starting from scratch in her analysis, she finds herself moving step by step through all of that code, which is very tedious and unproductive.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Users really don't want to be programmers.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

"Of course," you say. "Users certainly don't want to be programmers. That's a silly title for a best practice."

Ah, but I believe that most of us do actually (though subconsciously) treat our users as if they were programmers. Why do I say this? Because . . . .

• A fundamental task of a programmer is to test the individual programs that he writes. These are called unit tests.
• Most programmers do not test individual units, and barely test higher-level units, before they are integrated into the application as a whole.
• Most applications are, therefore, handed off to users for acceptance testing, with lots of untested program units.
• The end result: users find bugs that should have been found through unit testing, which means they are, in effect, performing unit tests, and that means they are, in effect, acting as programmers.

. . . which we all agree they don't want to do, so maybe we shouldn't force them to!

Problem: Sunita's team is dragged down off its pedestal of semi-godliness.

When Sunita first joined My Flimsy Excuse, the users all but bowed to her as they passed in the hallways. Sunita was a programmer, a wizard who made computers dance to her tune and made it possible for MFE to sell its products. Five years later, Sunita has noticed that she is more likely to get a surly glance and an abrupt nod, rather than a bright, thankful smile.

What's changed in that time? Well, her team has released 20 production applications. Surely that should be cause for celebration in the Land of the Users. So much functionality! So many screens!

And so many bugs.

As with so many other development organizations, Sunita's group has always worked under extremely tight deadlines and inadequate headcount. They struggled to implement all the functionality requested by the users. When it came to testing, they did what they could, but it was all done by hand and was very incomplete. They knew this, but what could they do?

What they did is what we all do: they gave the application to the users for "acceptance testing" and kept their fingers crossed in the hope that the users would not find too many bugs.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Do you take road trips without a destination in mind?

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: We cannot trust our own minds to fully and objectively test our code.

Delaware has done it again! On Monday, Sunita assigned him the task of writing a program to gather excuses from various web sites, collect them within an Oracle table, and then use PL/SQL to analyze and categorize those excuses. Just two days later, Delaware announces that he has completed the task. Amazing! Sunita thought it would take a week. "And you tested it?" she asks him warily. "Absolutely! I ran it through its paces," he answers.

Sunita nods and asks him to email her the code along with the test script. It arrives in moments, and when she runs the script against some dummy web sites it seems to work. But then she decides to change the test environment. All the dummy web sites now hold nary a single excuse on them—there is no data to mine and retrieve. When she runs Delaware's excuse_me_now program, it raises an unhandled exception: NO_DATA_FOUND.

When Sunita asks Delaware about this, he slaps his forehead: "Duh! How could I forget to test that?" And he looks rather silly in front of his boss. Hey, at least his sloppiness wasn't caught by the users!

Surely you've had a similar experience. You write a program. And then you test it. Lots. You really do. And yet, still, when you hand it off to QA, or to the users, or to anyone else, they hit bugs right away, and you look like a total amateur. What's going on here?

Basic human psychology is the culprit, in three ways:

We tend to take the path of least resistance (effort) whenever possible

Without an automated tool, testing is a tedious, seemingly endless, and quite difficult task. Consequently, we put it off for as long as we possibly can. We tell ourselves that we need to write the program before we can even think about testing. And with that rationalization, we sneak back to our comfortable world of algorithms (logic puzzles). Later for testing!

We so badly want/need our programs to work that we practice avoidance

Even with a good tool, we still avoid testing if at all possible, because testing is all about the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

For every test you can think of, there are 10 tests waiting to be performed.

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Problem: Lizbeth has entered a medical condition called the Test-By-Hand Coma State.

Sunita recognizes that Delaware is not a very good tester, so she turns to Lizbeth and asks her to finish testing the excuse_me_now program. Being very diligent, Lizbeth sits down and thinks about what else must be tested. She implements these test cases in Delaware's script, and runs the expanded test.

While doing that, she thinks of a few more tests, writes the code for those, and tests again. She changes excuse_me_now when she finds bugs, runs the test aga