Monday, July 15, 2013

PL/SQL Coding Standards, revisited

Formatting is the least important aspect of Coding Standards. Unfortunately, most sets of standards expend an inordinate number of pages on the topic. Because:

  1. The standards are old, or the person who wrote them is.
  2. Code formatting is an easy thing to codify and formalise.

Perhaps the source of most wasted energy is formatting keywords. Back in mediaeval times, when the only editors in use were vi or Notepad, or perhaps PFE, this was a pressing issue. But modern editors support syntax highlighting: now that we can have keywords in a different colour there is much less need to distinguish them with a different case.

Personally I prefer everything in lower case; I save about 23 seconds a day from not having to use the [shift] key. But other people have different preferences, and for the sake of the team it is better to have all the source code in a consistent format. But the way to achieve this is with automation not a Word document. SQL Developer, PLSQL Developer and TOAD all have code formatters (or beautifiers, yuck) , as do other tools. Let's put the rules into the machine and move on.

What should the rules be? Well, everybody has an opinion, but here are my definitive PL/SQL Coding Standards, with an addendum of formatting guidance.

APC's Damn Fine PL/SQL Coding Standards



  1. Your code must implement the requirements correctly and completely.
  2. Your code must have a suite of unit and integration tests (preferably automated) to prove it implements the requirements correctly and completely.
  3. Your code must implement the requirements as efficiently and performantly as possible.

APC's PL/SQL Code Formatting Guidelines



  1. Case. ALL CAPS is Teh Suck! Anything else is fine.
  2. Indentation. Align consistently. Spaces not tabs. Four spaces is the Goldilocks indent.
  3. Short statements. One statement per line.
  4. Long statements Use line breaks, don't make me scroll.
  5. Naming conventions. Use prefixes to distinguish local variables, global variables and parameters from each other and from database objects.
  6. Comments. A comment is an apology.
If you prefer something less minimal, William Robertson's PL/SQL Coding Standards remains the most complete and best annotated set on the web. Okay, so he does specify "3 spaces for each nesting level" (why? computing is all about powers of 2) but nobody's perfect.

Labels: ,

Sunday, December 02, 2012

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.

Labels: , , ,

Wednesday, March 12, 2008

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.

Labels: , , , ,

Friday, November 02, 2007

My PL/SQL Coding Standards

As I mentioned recently a long-running trolling thread in the OTN forum has recently taken a new twist, by reviving the PL/SQL Coding Standards meme. Alas I was wrong in my prediction that the OTN moderators would soon kill the thread. Not only is it still going but the evil genius behind it is still going, and regularly changing their handle. Currently it is my turn to be impersonated.

In order to justify my assertion in that thread I have decided to publish my damn fine standards. So here they are.


APC's Damn Fine PL/SQL Coding Standards



  1. Your code must implement the requirements correctly and completely.
  2. Your code must have a suite of unit and integration tests (preferably automated) to prove it implements the requirements correctly and completely.
  3. Your code must implement the requirements as efficiently and peformantly as possible.



Is that it?


These standards have much to recommend them. They are easy to read. They won't need revision whenever there's a new version of PL/SQL. And they focus on what is really important in code: correct functionality. Of course things such as layout and naming of variables are important, I'm not saying they're not. But a PL/SQL procedure can be neatly laid out, rigourously capitalised and thoroughly commented and yet be full of bugs. In my experience, most coding standards tend to document in tedious detail the things which are easy to standardise - use of upper and lower case, line indentation, etc - rather than the things which actually matter.

Also the strictures of codings standards are rarely revised. I still get handed coding standards which say things like "Always use explicit cursors". So either these standards were written ten years ago or the author has not coded any PL/SQL in the last ten years or the author should be shot. Whichever it is, if the standards document contains such canards, why should anybody pay it the slightest attention?

I intend to expand upon some of these points in future posts. If you want a more regular set of PL/SQL Coding Standards then you should check out the redoubtable William Robertson's site. Alternatively, invest in a copy of Oracle PL/SQL Best Practices by Steven Feuerstein (Whom God Preserve). I don't agree with everything that Steven writes but I firmly believe that if every PL/SQL coder in the world followed these guidelines the overall quality of the global PL/SQL codebase would increase by several orders of magnitude.

Update


The troll has now reverted to an anonymous userNNNNNN handle and restored the original text. Obviously even they have got tired of the joke. Or been stricken by conscience.

Labels: , , , , ,

Wednesday, October 17, 2007

It's That Thread Again

There's a thread which has been running in PL/SQL for several months. It started off as a spoof on the URGENT PLZ HELP type threads we get from time to time. I blogged about it in June. After a long hiatus the OP has changed their handle to John Titor, Time Traveller and posted some more nonsense. I don't mind that. Some of the responses were quite entertaining, and the thing was mostly harmless.

The problem is, they are now masquerading as venerable lead-pipe swinger Billy Verreynne. They did this by the simple mechanism of replacing lower case Ls with capital is in their handle(curse those sans serif fonts!). Furthermore they have changed the subject matter and text of the original post to make it look as though Billy is offering Oracle's own internal PL/SQL coding standards. This is an homage to a notorious thread from the forum's history. This leads credence to the suggestion that the post is a mischievous forum regular with a sound knowledge of the other denizens. Unfortunately some of the less knowledgeable visitors are falling for this scam and are posting their e-mail addresses.

This is a thread which now, alas, has to die. Anybody got any zombie poison?

Post Scriptum


I haven't bothered putting a link in to this thread because I expect it to be pulled soon enough.

Labels: , ,