News, views, tips and tricks on Oracle and other fun stuff

The Case of Better Readable Code

When you code, you write your programs either in all lower case, all upper case, or a combination of the two. Let’s take PL/SQL for example, almost all PL/SQL programming best practices and coding styles that I’ve come across recommend using upper and lower. The following is an excerpt from the book Oracle PL/SQL Best Practices By Steven Feuerstein:

PL/SQL code is made up of many different components: variables, form items, report fields, procedures, functions, loops, etc. All these fall into two major categories. Reserved words and program specific identifiers. Reserved words are those language elements that are used by PL/SQL. They have special meaning to the compiler and hence are reserved. Program specific identifiers are the names that a programmer gives to the various components of program such as variables, constants, procedures etc…

The PL/SQL compiler treats these two types of text very differently. You can improve the readability of the code greatly by reflecting this difference in the way the text is displayed.

To distinguish between reserved words and program specific identifiers, use of the upper and lowercase strategy is recommended. Use all UPPER case of reserved words and lower case of program specific identifiers. This increases the readability of the code.

Sure, this does increase the readability of the code. But, it sure does take extra time to think what words are reserved and what words are program specific identifiers and decide which case to use accordingly, upper or lower. Even if you know the reserved words by heart, it is still going to take you extra time to hit that Shift or the Caps Lock key every time you “switch context”.

So, unless you do not care about enhancing the readability of your code or you do not mind the extra time it takes to manually decide which case to use, I believe that an automated code formatter is an essential tool that every developer should have.

I use Quest SQL Navigator’s code formatter. It’s very powerful and has many formatting options:

Oracle SQL Developer also has a formatter, but it has far less formatting options than SQL Navigator. It is one area where Oracle SQL Developer needs improvement.

Filed in Interesting, Oracle on 11 Mar 07 | Tags: , , , , ,

Reader's Comments

  1. |

    I’m a fan of init caps – there’s a reason why directions signs on freeways/motorways don’t use all lower case or (worse still) upper case letters, and it’s because the human brain is better as interpreting when you give more visual clues to the words.

    eg. from easiest to read to hardest …

    Exec DBMS_Stats.Gather_System_Stats

    exec dbms_stats.gather_system_stats


    Mind you, it drives other people crazy, but that’s just another advantage for me šŸ™‚

  2. |

    That’s a good argument for automated code formatting, Dave. I can reformat your code to look the way that I find most readable. Eddie – I use the code formatter that comes with TOAD – looks like it is the same one you use with SQL Navigator – interesting, but not surprising – both tools are from Quest.

  3. |

    because the human brain is better as interpreting when you give more visual clues to the words

    Exactly. Another example:

    1. select employee_id from employees
    2. Select Employee_Id From Employees
    3. SELECT employee_id FROM employees

    I believe that number 3 is the easiest to read. There is a visual clue that SELECT and FROM are reserved words.

    both tools are from Quest

    Right. I’m not surprised either that they use the same formatter.

  4. |

    Personally, and this is very much IMHO, I don’t get the reserved word thing. The list changes with each release and with each product (SQL, SQL*Plus, PL/SQL) and if one is supposed to recognise them well enough to capitalise them then the capitalisation ought to be regarded as redundant.


  5. |

    I’m with Eddie: “Select * From” is to my particular brain cluttered, twee and unreadable. I would sooner see all-lowercase, or else (as I do) routinely go through PL/SQL Developer’s .kwf files adding words like COLLECT and NTILE so they show up properly.

    Although I do use code formatters, my problem with them is that the default settings do criminally insane things like right-aligning SQL keywords, and they can’t seem to do fairly obvious things like starting a new line for a subquery.

  6. |

    I’m with David on this.

    With SQL, if it is not in quotes, I write it lower case. The exceptions are when I pass the code through a formatter.

    If I happen to be writing PL/SQL that is to be of any consequence, I may name my variable with init cap on each word, with the initial character in lower case. eg. thisIsMyVariableName

    If it is some really serious PL/SQL (more than a 100 lines or so ) the variables will probably be prefixed with character to indicate the data type.

    eg. dThisIsADateVariable

  7. |

    The point I’m getting here is that you can never expect everyone to like the same coding format that you like and vice versa. This makes the need to enforce code styling and formatting standards (among team members) even stronger.