PL/SQL Formatting : More pearls of wisdom from Bryn


glasses-272399_1280-smallAnother topic of conversation that came out of Bryn‘s session at Oracle Midlands related to PL/SQL code formatting. I’m not sure I agree 100% with his opinion, but it’s certainly making me think about my current stance.

When I said I was going to blog about a couple of the points he raised, Bryn emailed me some stuff, which I’m going to use here. So this is Bryn’s content, with some of my opinions thrown in. I will no doubt get told off for not doing the subject justice, like I did with the last post. 🙂

Bryn: Look at page 1 (below). This makes PL/SQL look so old-fashioned. I’m sure that this is part of what makes it unappealing to the modern youth. How easy do you find it to read its real meaning?

Me: With a few minor formatting changes, this is pretty much what I do. 🙂 As a result, it looks really normal to me and I like it, but if I try to put myself into the head-space of a young person, it does look overly formal, and as Bryn said in his talk, we are essentially shouting all the keywords and using naming conventions that detract from the true meaning we are trying to convey. I don’t like to admit it to myself, but I kind-of see his point. It does look a little Pipe and Slippers.

Bryn: Now look at Page 4 (below). Identifiers are like proper nouns in English prose. They’re capitalized to let them stand out. The capitals in Object_ID (or DBMS_Output) are an aid to pronunciation.


Me: This is a slightly unfair comparison, since there is no syntax highlighting, which is on by default in all PL/SQL IDEs, but it does look less “old”. I understand Bryn’s point about the capitals potentially aiding pronunciation, but if I’m going to stop “shouting” the keywords, I actually prefer the look of it all in lower case myself. 🙂 I agree that identifiers still need “_” between words. Although camelCase is used by many languages, it doesn’t work so well in Oracle. It looks fine in your scripts, but when you are looking at dictionary views, much of the information is presented in uppercase, so “uniquenessContraintMissing” becomes “UNIQUENESSCONSTRAINTMISSING”, which sucks. The prefix/suffix used on identifiers can be confusing, especially since an alternative was mentioned in the previous post.

Bryn: As promised, here’s my-marked up copy of the Gettysburg Address. It’s considered to be an exemplar of English prose, so having it mangled into an incomprehensible mess by the robotic application of “code formatting best practice” makes my point very vividly. … Feel free to use it. But do make sure that you credit the author, President Abraham Lincoln, and me for having had the idea to use it to make fun of the anally retentive code formatters who jointly conspire to make PL/SQL look so dated.



Me: Yeah. Point made! 🙂

I’ve tried to alter my code formatting in the past, which invariably meant I had to go back and reformat it a couple of days later when I couldn’t stand knowing it wasn’t “how I usually do things”. 🙂 Having said that, if you read the last blog post, you would see I tried to stop shouting. 🙂 It’s not what Bryn likes, but I think it’s a step in the right direction… Maybe…

Am I going to redo all my articles, blog posts and videos? No. Am I going to try and change? At the moment I’m thinking yes, but I reserve the right to do a u-turn at any point.

Don’t forget to check out these whitepapers from Bryn.



Author: Tim...

DBA, Developer, Author, Trainer.

14 thoughts on “PL/SQL Formatting : More pearls of wisdom from Bryn”

  1. This is like battle linux vs. windows, nvidia vs. amd. Many of my younger coworkers don’t give a shit about formatting the code and for me as I have some “standards” this is then like looking on a puzzle. I agree with you if it can’t be by the “standards” at least have it all in lowercase. Will I change? Probably not, I’m used to write a code in some way. Is it a problem for me to read some puzzle code? Definitelly 🙂

  2. Yeah, OK, so Bryn HATES the way I format my PL/SQL code. Ah well. As for appeal to youth: that’s an interesting point I hadn’t heard from him before. Not sure it is a big deal really, compared to the other reasons people choose their programming languages.

    One other thing to consider: I will never adopt a formatting style that is not supported by automatic formatting. Ask Bryn how much time he takes to make his code look presentable to teenagers! 🙂

  3. For me… its lower case all the way by default. That way, if there are some particular elements that really need emphasis (eg a vital comment) then the use of some/all caps really makes it stand out.

    But personally… I love it when developers say they “cannot maintain code” because it doesnt conform the company standard. Seriously ? If your skillset is limited by your ability to look past whether something is upper/lower/mixed…then wow… I’m not particularly inspired by the likely quality of your output 🙂

  4. Consistency is the most important thing for me, as long as someone’s code uses consistent formatting it’s easy to read and understand. Personally I prefer uppercase keywords and lowercase everything else. The keywords, which are normally shorter, provide the structure, are easily recognised and filtered out; lowercase words are easier to read, and the identifiers are the bits that need a little more time to read. For example – which is easier to visually parse quickly: “SELECT selected FROM form” or “select selected from form”?

  5. It’s probably worth considering that PL/SQL is a wordy, non-terse language – it uses words where other languages use punctuation, e.g. THEN, WHEN, END, etc. – so capitalising these structural elements makes it easier to gloss over them and focus on the logic. IMHO 🙂

  6. Food for thought… some interesting points.

    But is it style which is putting people off?

    Bigger problems are usually:
    1. The WTF implementation of the logic.
    2. The performance

    Perceptions are: Oracle is too complex. Thinking in sets is just too hard. Row-by-row in any language is too easy. Just scale horizontally!

  7. (1) Rewording what Peter said slightly, many people who write lots of prose which is read by lots of people don’t give a shit making it easy to read.

    (2) To answer Connor, I’m insulted by the big manager who’s so important that she can’t spare the time to press the shift key. Does anyone think that all lower case emails are easier to read than ones that use case as the rules of the language they’re written in require? The more that reading code is like reading English (no apologies here, the keywords are in English), the more understandable it is.

    (3) To answer Steven’s “how much time do I take to make my code look presentable”? I simply type it in. I often I play with the whitespace in successive declarations, or similar, to give visual emphasis to similarities and differences. I don’t have rules; I use my aesthetic judgment. No robot formatter could do this. I certainly spend a lot of time reading and re-reading my code, aiming to improve its understandability. In this connection, I spend a huge amount of energy on the choice of the names that I invent. These are the important things. They need to be readable. And the spelling needs to give a clue to the pronunciation. (Hence DBMS_Random for Dee-bee-em-ess Random.)

    (4) To answer Jeffrey, this seems easiest to me, ‘cos it’s closer to ordinary English prose:

    select Selected from Form

    But (see my #3) I would never choose such silly names. And to answer Jeffrey’s “capitalising these structural elements [like THEN, WHEN, END, etc] makes it easier to gloss over them, I claim the exact opposite. That was the point of the robotically formatted Gettysburg address. Seems self-evident to me that those shouted red words get in the way of smooth reading — as, of course, so also do the prefixes that denote the part of speech (formal parameter, variable) on the other words.

    (5) To answer Dominic, correctness is essential. Only when this is established is it time to consider performance. This matters too, of course. And so does maintainability. Correctness, optimal performance, and easy maintainability are most likely when the code is written by an author who cares about the craft of communicating. Oracle Database is as complex as it needs to be. If it were less so, it would sacrifice functionality and anyway wouldn’t work.


    grant Select on Data_Owner.Item_Prices to Code_Owner

    is to

    GRANT SELECT ON data_owner.item_prices TO code_owner


    Only when it this established is it time to consider

    is to

    only WHEN THIS IS established IS IT time TO consider

    Having done something a certain way for ever is an insufficient reason for continuing to do it that way…

  8. Personally I have never understood the most common PL/SQL formatting standard of SHOUTING the keywords, the standard words of the language. We know those bits, we can almost ignore them. What is important to understanding the code are the variable names, the block or loop labels we include, maybe even the exceptions we catch. The stuff that is specific to that single program. I’m leaning more and more towards using lowercase for everything except comments or function constructs I know need to be looked at twice. Upper Case should mean “pay attention to me”.

    But I know I am in the minority (and I don’t understand why, it seems so logical to me that it is wrong to highlight SELECT, FROM, WHERE, LOOP, END, BEGIN as we all use left justification to mark the transitions in the code ). So I try and just repeat any standards already used in any code I have to modify. If I am on a large project I will make a plea for less Uppercase but then just stick to the standard agreed.

    I don’t think the formatting of the code is a main factor in appealing (or not) to the cool kids. They are attracted to what other cool kids tell them is New and they hear lots of other cool kids talking about. It’s called “fashion” – and I never got that either (ie clothes fashions or music fashions).

  9. As a PL/SQL developer, I find the topic very interesting. Especially after reading the above comments.

    I like to write code – and also indent and document it – to be as easy to read, understand and change as possible. This is more important than any fashion for me.

    About the formatting debate between upper case, lower case and capitalisation well, I’m gonna throw a fourth way. I usually write the above mentioned grant like “grant select on DATA_OWNER.ITEM_PRICES to CODE_OWNER” because I like to write in upper case the dictionary objects and not the keywords. Of course, I do this when I am the owner and I can set the standards. If I have to change something in a big projects I stick to its formatting standards.

  10. There is some science behind this. That’s why road signs on free/motor-ways in many countries use mixed case. That’s why the terms and conditions you have to agree to before you download software uses all caps: they want to put you off from reading it. It’s easy to find the studies using Internet search. Please do so. Here’s a couple of examples:

    Notice this in the Wikipedia piece: <>

  11. Notice this in the Wikipedia piece:

    “All-capital print greatly retards speed of reading in comparison with lower-case type. Also, most readers judge all capitals to be less legible. Faster reading of the lower-case print is due to the characteristic word forms furnished by this type. This permits reading by word units, while all capitals tend to be read letter by letter… The use of all capitals should be dispensed with in every printing situation.”

  12. Apart from the fact that it is prose, not computer code, the main problem I have with the Gettysburg Address example is the silly prefixes, v_, p_ etc. Whether keywords should be uppercase or lowercase is debateable (I stand by my earlier comments) but if we can all agree to get rid of all a_hungarian n_notation I’d be ecstatic 🙂

  13. Jeffrey: I agree that there may not be a direct comparison between prose and computer code. As Martin said, the left justification of most keywords makes a big difference anyway.

    For me at least, consistent indentation is the biggest help in clarity. Now admittedly, I’m somewhere towards the wrong end of the spectrum for dyslexia, but the shape of the code is what draws my attention, not so much the words. I’m not able to scan read (code or prose), so maybe I don’t notice the problems others do. 🙂



  14. As Jeffrey pointed out, my robotically formatted Gettysburg address made two points. The first was just a throw-away about SHOUTING. I had though that the silliness of uppercasing keywords was self-evident. Apparently not. And don’t get me started on SUBSTR, UPPER, LOWER, and the like. These texts, in source code, are identifiers, and not keywords. Then there’s DBMS_OUTPUT.put_line. What’s that about? Someone might’ve said that they like to shout every single thing that Oracle Corp has named. But we don’t even have that consistency with the pointless convention here.

    Anyway, enough about upper and lower case now.

    What about the second point that Gettysburg made: (in Jeffrey’s words) the the silly prefixes, v_, p_ etc? Indeed! This was meant to be its main point. Tim drew attention to some code that showed the use of qualified identifiers in his “You learn something new every day!” post. That code’s purpose was exactly to point out that the notion that such conventions might prevent name capture is bogus. It might have been an effort to persuade non-believers on this point before version 11.1. But, with the advent of fine-grained dependency tracking, this is all laid bare: qualify every identifier in a static SQL statement and your unit stays valid when a column is added to any table that it references; rely on v_, p_ etc. and it goes invalid. This is because Oracle Database can’t trust you to follow self-imposed conventions; rather, it must assume that with unqualified identifiers, it’s possible that adding a column will cause a PL/SQL identifier to be captured by that column.

    This is all explained in my 2008 “Doing SQL from PL/SQL: Best and Worst Practices” paper.

    Look out for a new blog post from me on the topic of name resolution in PL/SQL and SQL fairly soon. I’ll address more of how you can qualify identifiers to advantage that I did in my 2008 paper. And I’ll diffuse Jonathan Lewis’s concern (“There goes your last chance of getting people to use meaningful function and procedure names”) by showing how to use a tightly enclosing labeled block statement to establish what effectively is an alias for the identifier for your long, meaningful name.

    I’ll tweet when it’s posted.

Comments are closed.