How PL/SQL Development Standards work

I have been gigging at a place which has documented PL/SQL Development Standards. This is not so unusual: most Oracle shops have such a document. What makes it unusual is that they enforce the standards. With code reviews. And I mean properly enforce: programs fail QA for egregious breaches of the standards or a sufficient accumulation of minor breaches. This is less common than it ought to be.

Many coders are sceptical about development standards; I have been in the past. Standards generally focus on things which are easy to standardise (indentation, case, naming conventions) rather functional correctness or design principles. They frequently codify arbitrary or outdated practices (mandating explicit cursors is a particular bugbear of mine). They either go into so much detail that they are unreadably long (and dull) or are so sketchy that they operate as easy-to-ignore guidelines. But I think many experienced developers' objections boil down to: I don't like being told how to write my code; my style is the best style; my code is clean, clear and readable.

The catch is, readability is not simply a function of personal style: it emerges from consistency across the entire codebase. Just because I find my personal coding style clear doesn't mean everbody else will. At the very least a colleague reading my program will have to invest time in understanding how I name my variables, how I use table aliases, and a dozen other things, none of them important individually but all together adding friction to the crucial task of understanding how a program works (or does not work).

This particular set of standards certainly had a lot to say about layout. Many strictures fitted with my natural coding style (all lower case, one column per line in a SQL projection, comma before the column name rather than after it). Others were rather tiresome: the rules for clause alignment entail a lot of spacing and backspacing to ensure elements line up. There are a few strictures I actively disagree with (notably mandatory use of SQL-89 syntax i.e. impicit joins). But here's the rub: I didn't get to pick and choose which of the standards I followed. I just had to knuckle down and follow them all. Because the discipline of the code review meant my programs failed QA when I hadn't applied the standards.

There's more to consistency than just layout and naming conventions. There's also functional consistency: use of SQL and PL/SQL idioms, how to organise programs within a package, and so forth. Too many things to cover in a single document. But again, code reviews enforce standardisation of these aspects, by applying undocumented conventions with the same rigour as documented standards. A couple of times I tripped over such an undocumented convention and it didn't feel fair: my code failed the review because I wrote something which was wrong even though not explicitly covered by the standards. One of these times it was something awry in the layout. "That's wrong", the reviewer said. It was a difference I hadn't even noticed, and probably you wouldn't have noticed either, and even if I had have noticed it I wouldn't have thought it was wrong. But it was different from what everybody else was doing. That made it wrong.

Everybody undertakes code reviews and everybody's code is reviewed. Thus code reviews shape the codebase, by enforcing documented standards and undocumented conventions. As a result this is the most readable codebase I have ever worked on. It's almost impossible to tell who wrote any given program, because all programs look the same. It's easy to reason about a piece of code because it follows rigorous naming conventions and consistent architectural principles. The code is habitable. A colleague can read a program I wrote and feel comfortable doing so. The layout, the naming conventions, the consistent selection of one approach in situations where PL/SQL offers more than one way of doing something, all these factors mean my program looks just like the program anybody else would have written. So the reader is freer to understand what the program actually does and how it works. Standardisation reduces friction.

It is a virtuous circle. Code reviews enforce a consistent programming style, which eliminates trivial (i.e. non-functional) differences in the program. In turn this makes the program easier to review: all the programs look basically the same which highlights the things which need to be different, the business logic and the data structures.

Readability is a feature

Readility is a feature. It's a feature our code must have. We all know readability makes code easier to maintain, easier to re-use, easier to debug. Yet still many developers bridle at the suggestion that their PL/SQL must look like everybody else's PL/SQL. I get this. It's not that I think the way I write PL/SQL is intrinsically correct, it just looks the way I have evolved to write it over the years. A new set of coding standards, rigorously applied, disrupts my flow. I must slow down to correct the variable names or fix the layout. It's tedious.

Tedious but also necessary. A sofware system is a shared enterprise. It's not "my" code, it's the project's code; I am just the person checking it into source control. As a discipline, programming is a craft not an art. PL/SQL is simply a device for turning data into business value. It's more important that other people on the team can work with our code than that it has our signature style. So let's not be precious about appearance. We must follow the rules, and save our self-expression for our poems and our tweets.

Above all, know this: there are no development standards without code reviews.

Error Wrangling

Last week the OTN SQL and PL/SQL Forum hosted of those threads which generate heat and insight without coming to a firm conclusion: this one was titled WHEN OTHERS is a bug. Eventually Rahul, the OP, complained that he was as confused as ever. The problem is, his question asked for a proof of Tom Kyte's opinion that, well, that WHEN OTHERS is a bug. We can't proof an opinion, even an opinion from a well-respected source like Tom. All we can do is weigh in with our own opinions on the topic.

One of the most interesting things in the thread was Steve Cosner's observations on designing code "to be called by non-database, and ... non-Oracle software (Example: Oracle Forms, or a web process)". He uses WHEN OTHERS to produce a return code. Now return codes are often cited as an example of bad practice. Return codes disguise the error and the allow the calling program to proceed as though nothing had gone wrong in the called unit. Exceptions, on the other hand, cannot be ignored.

But Steve goes on to make a crucial point: we must make allowances for how our code will be called. For instance, one time I was writing PL/SQL packages for an E-Business Suite application and I was doing my usual thing, coding procedures which raised exceptions on failure. This was fine for the low level routines which were only used in other PL/SQL routines. But the procedures which ran as concurrent jobs had to finish cleanly, no matter what happened inside; each passed a return code to the concurrent job manager, and logged the details. Similarly the procedures used by Forms passed back an outcome message, prefixed with "Success" or"Error" as appropriate.

This rankled at first but it is undoubtedly true that exceptions make demands on the calling program. Although the demands are not as high in PL/SQL as in say C++ or Java, raising exceptions still changes how the calling function must be coded. We have to work with the grain of the existing code base when introducing new functionality. (Interestingly, Google has a fiat against exceptions in its C++ code base, and developed its Go language to support return codes as the default error handling mechanism. Find out more.)

The point is APIs are a contract. On its side the called program can enforce rules about how it is called - number and validity of input parameters, return values. But it cannot impose rules about what the calling program does with the outcome. So there's no point in exposing a function externally if its behaviour is unacceptable to the program which wants to call it. When the calling program wants to use return codes there's little point in raising exceptions instead. Sure the coder writing the calling program can ignore the value in the return code, but that is why we need code reviews.

So, is WHEN OTHERS a bug? The answer is, as so often, it depends.

GOTOs, considered

Extreme programming is old hat now, safe even. The world is ready for something new, something tougher, something that'll... break through. You know? . And here is what the world's been waiting for: Transgressive Programming.

The Transgressive Manifesto is quite short:

It's okay to use GOTO.

The single underlying principle is that we value willful controversy over mindless conformity.

I do have a serious point here. Even programmers who haven't read the original article (because they can't spell Dijkstra and so can't find it through Google) know that GOTOs are "considered harmful". But as Marshall and Webber point out, "the problem lies ... in the loss of knowledge and experience. If something is forbidden for long enough, it becomes difficult to resurrect the knowledge of how to use it."

How many Oracle developers even realise PL/SQL supports GOTO? It does, of course. Why wouldn't it? PL/SQL is a proper programming language.

The standard objection is that there is no role for GOTO because PL/SQL has loops, procedures, CASE, etc. But sometimes we need to explicitly transfer control. In recent times I have have across these examples:

• a loop which raised a user-defined exception to skip to the END LOOP; point when the data was in certain ststes, thus avoiding large chunk of processing. A GOTO would have have been cleaner, because it is poor practice to represent normal business states as exceptions.
• a huge function with more than a dozen separate RETURN statements. GOTOs directing flow to a single RETURN call would have been really helpful, because I needed to log the returned value.
• a condition which set a counter variable to a large number so as to short-circuit a loop. Here a GOTO would simply have been more honest.

These examples are all explicit control transfers: they cause exactly the sort of random paths through the code which Dijkstra inveighed against. But the coders didn't honour the principle underlying his fatwa, they just lacked the moxie to invoke the dread statement. Instead they kludged. I'm not saying that using a GOTO would have redeemed a function with 800 LOC ; clearly there'e a lot more refactoring to be done there. But it would have been better.

Here is a situation I have come across a few times. The spec is to implement a series of searches of increasing coarseness, depending on which arguments are passed; the users want the most focused set of records available, so once a specific search gets some hits we don't need to run the more general searches.

Nested IF statements provide one way to do this:

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);

    if result_set.count() = 0
    then
        result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);

        if result_set.count() = 0
        then
            result_set := sieve_3(p1=>var3, p2=>var5);

            if result_set.count() = 0
            then
                ....
            end if;
        end if;
    end if;      
    return result_set;     

Obviously as the number of distinct searches increases the nested indentation drives the code towards the right-hand side of the page. Here is an alternative implementation which breaks the taboo and does away with the tabs.

    result_set := sieve_1(p1=>var1, p2=>var2, p3=>var4, p4=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    
    result_set := sieve_2(p1=>var2, p2=>var3, p3=>var4);
    if result_set.count() > 0
    then
        goto return_point;
    end if;

    result_set := sieve_3(p1=>var3, p2=>var5);
    if result_set.count() > 0
    then
        goto return_point;
    end if;
    ...
    << return_point >>
    return result_set;     

I think the second version has a clearer expression of intent. Did we find any records? Yes we did, job's a good'un, let's crack on.

tl;dr
GOTO: not as evil as triggers.

Idle thoughts of a idle coder

Brian Tkatch has launched a thread on the PL/SQL forum about enhancements to SQL which would just basically save some typing: Things i wish SQL supported. The lazy man's list. This is quite a revealing thread, because it is always interesting to see what shortcuts people would like to take. It's a bit like peeking inside the medicine cabinet in other people's bathrooms (not that I would ever do that).

My personal wish is for:

select * {-empno} from emp;

That is, select all columns from the EMP table except EMPNO. This would be particularly useful for querying tables with BLOB columns in SQL*Plus.

As the thread as grown it has turned into a discussion of SQL theory ("conceptually, (using Venn diagrams) the tables/views are the circles, and the predicates define in what way the circles overlap") which requires too much concentration. The thread was supposed to be about laziness!

The patron saint of programmer laziness is Larry Wall, the inventor of Perl:

"The virtues extolled for Perl programmers are laziness, impatience, and hubris. Together, these admirable characteristics have led to the creation and use of many publicly accessible Perl modules. Because of laziness, programmers would rather write modules than repeat a procedure over and over (and would rather use modules written by other people than write new code from scratch). Because of impatience, programmers write consolidated code that is flexible enough to anticipate their future needs. And because of hubris, programmers share their triumphs with the rest of the Perl community and continually tweak their modules until they're the best they can be."

The problem with proactive laziness is that it can be hard to estimate how much effort will be saved later by putting in some extra effort now. Plus, writing automating utilities and code generators can just be a seductive form of procrastination. It feels like work but we aren't moving forwards. In the end we spend so much time sharpening the axe that we never get around to cutting down the tree. So the trick is to only automate the things we know it will be worth automating. This means doing something the plain way at first. Only when we get to the second or third cut'n'paste should we consider whether we need a parameterised module instead. The important thing is to automate early, in order to derive the maximum return on the work.

I am currently practicing cut'n'paste programming in a test data generator. I could refactor my code to drive off an array but re-editing my package to populate a collection will be a PITA. I should have done it some time ago, but I failed to realise just how many additional datasets I was going to need. At this point the ROI on the automation is quite small. So I have chosen to continue paying the find/copy/edit tax rather than spending half a day to figure out a better way of doing things. In the long run I will have expended more effort but in the meantime I keep making progress towards the main goal.

Update

Over on the Artima site Jeremy Meyer has written an article on Why it is better to be lazy.

Code quality metrics

Whenever two or three developers gather together they will argue over the best tool for editing code. Once they've chewed the spearmint out of that they will commence a heated discussion on ways to measure code quality. But I think this cartoon by Thom Holwerda pretty much nails it.

Neologism corner: commando

Somebody who chooses to use a command line tool like sqlplus.exe over a GUI.

I coined this unthinkingly in a Forum thread about SQL editors which had descended into another "real programmers use vim" yawn fest. However, Brian Tkatch picked me up on it, so I had to retrofit a justification.

I chose the word because a commando is a member of an elite unit of toughnuts, which would seem to fit the self-image of soi disant real programmers. It was only afterwards that I noticed the resonance with "command line" itself. And it also brings fresh life to the phrase "going commando".

Update

The ever-reliable web-comic xkcd proffers a neat encapsulation of how these conversations tend to go.

One of those days

My main task for today was to pick up some zipped files from a server on the client site and bring it back to the office to load onto our development server. Simple enough task.

Except that the network connection was a bit brittle. WinSCP kept failing at 99% complete. The same files were available on the QA server. Alas that box was down: Support hadn't noticed until I mentioned it. So it was back to the first server. I found that if I copied a single zip at a time I could at least keep track of the failures and re-copy when necessary.

Eventually I had all the files I needed. It was then a matter of burning them to CD. Inevitably the desktop I was using didn't have a CD burner but fortunately there was another a PC in the office which had a burner and could see my networked TrueCrypt folder (it was potentially sensitive stuff).

Back at my office I discovered that my access to the shared development network was locked. This happens from time to time because, well, just because. It mattered today because our two sysadmins had already left for the weekend (to Wales and France respectively) so there was no chance of me getting my account unlocked before Monday. So one of my co-workers had to transfer the files to the network. I couldn't actually do anything with the data but at least I would be able to erase the CDs.

While the transfer was happening I went to get a coffee. The coffee machine was out of coffee. Grrrr.

I recount these woes not because they are necessarily typical of my working day (some days I really get lots done) but simply to illustrate a larger point. There's a recent article on the Artima site discussing the impact of languages and frameworks on programmer productivity. The sad fact is only a relatively small part of a developer's day is actually spent coding. There are meeting to attend, cranky networks to wrestle and tea bags to be dunked because the coffee machine's on the blink.

Programming: is it an art?

Ming Chow writes in his O'Reilly blog that a computer program is no different than a novel. Hmmm. Obviously both books and programs comprise words ¹ whose organisation is governed by syntax; both are written. But as activities writing a novel and writing a program are completely different.

For a start novelists are writing a story to please themselves. Programmers, on the other hand, are writing a set of instructions for a machine to meet their users' requirements. Novelists work on their own. Programmers usually work as part of a team. This means their work has to conform to common standards and have a consistent presentation. The only writers who have to work like that are a bunch of Parisian surrealist poets playing the Exquistite Corpse game.

I would particularly take issue with this statement:

Source code should be read like a novel, and great programs should be planned so like any great novel.

I don't think source code should read like a novel. Even back in the days when I was writing COBOL the code was modular. In the modern world of object-oriented programs, loose coupling, configuration files and code re-use any resemblence to linear narrative has gone right out the window.

Furthermore, few novelists do plan their novels in any great detail. Rather they start off with an initial idea, invent a character or two and see what happens next. It's not unusual to hear a novelist say in an interview that they were surprised by what one of their characters said or did. Most have only the vaguest idea of how the plot will resolve itself until they get to the end of the book. Which, on reflection, does sound a lot like eXtreme Programming. That doesn't do lots of planning up front either.

As someone who studied history at university rather than a scientific discipline I'm alert to the possibilities of regarding programming as an art. Although there are some similarities some similarities with novel writing I would regard film-making as a much more analogous activity. There's teamwork, the studio system, the need to meet a budget, open source as a Woody Allen/Russ Meyer independent movie maker, etc. Indeed, watching Lost In La Mancha, a documentary about Terry Gilliam's failed attempt to film Don Quixote was just like watching a film about a computer project crashing. But writing software is primarily an engineering activity. I find I get much more insight from pondering the metaphorical implications of structural engineering, classical architecture and town planning. These all feel like activities that have a greater bearing on programming than reading a biography of Charles Dickens.

---

^{1. Except for languages like BF of course. (back)}