Naming and Coding Standards for SQL and PL/SQL – Some things I cant agree

As written by William Robertson (based on Steven Feuerstein), by using coding standards supports programmers in

  • Clearer Understanding
  • Better Code
  • Fewer decisions to make
  • Easier Maintenance

Especially the document “Naming and Coding Standards for SQL and PL/SQL” perfectly describes many coding standards for Oracle’s PL/SQL language.

But there are also some aspects I don’t agree with them…

… ok, they are pretty good and also evangelists in PL/SQL, but anyway …

Identifier naming conventions

This specification is really good: <Scope><Type><Primary Identifier><Modifier><Suffix>
The samples look like this:

  • g_loop_count
  • cp_employee_id
  • p_duplicate_account
  • get_something_out(p_data in varchar2)

Pretty nice, but why are we using this kind of old-fashioned underscores?
I personally think, today we could use the camel notation.
So let’s leave the underscores away and write all this stuff in mixed case (like a camel):

  • gLoopCount
  • cpEmployeeId
  • pDuplicateAccount
  • getSomethingOut(pData in varchar2)

I think this looks much better. Don’t you? If not, please tell me why.

Indentation

I would ony use SPACES, never use TABS. We’ve found out that switching IDE tools or SQL developement tools are handling this TABS stuff a different way. It looks good in IDE (a) and looks awfully in IDE (b)

Using case to aid readability

Use lowercase for all keywords. As mentioned above, all user defined variables, attributes, functions, … can be written in camel notation (mixed case).

Why using UPPER CASE keywords? I don’t know. Most SQL and PL/SQL IDE’s do provide keyword highlighting.
Printing code? Ok, no highlighting. But how many times do you really print your program code?

Conditional Statements

It’s been recommended to use IF x = 1 instead of IF (x = 1).
I absoutelly disagree!

Ok, it’s redundant bracketting and more work to do. Besides, do you also write some Java and PHP code?
Probably yes. Do you sometimes get some syntax errors? Never? Ok.
I do.
So I try to write the same syntax in all programming languages I use.
Java, PHP and PL/SQL: if (x=1)

All the other naming convention stuff is just great!
See link at the top of this post …

2 thoughts on “Naming and Coding Standards for SQL and PL/SQL – Some things I cant agree”

  1. William Robertson

    Thanks for the link.

    I used to have stronger feelings about CamelCase, but these days I’m not so bothered as long as the names are understandable: I hate “c1” for a cursor name, I would use “c_employees”, but I guess I could live with cEmployees.

    I think problems arise in stored names – tables, packages, public package procedures etc – because the names are stored in the data dictionary in uppercase, and so that is how any reports or IDEs have to list them. I don’t want the readability to depend on a particular capitalisation that is not necessarily available to everyone.

    This is different in a language such as Java, for example, where the names are case-sensitive and capitalisation has standard, useful meanings. In Oracle names are not case-sensitive and generally the class/object distinction is not relevant (a table is not an object) although I guess object types are an exception to this.

    btw I moved the site – it’s now http://www.williamrobertson.net , although the old blueyonder address will now redirect you.

  2. hi William
    ok, this is a good point. Oracle’s data dictionary really stores everything in upper case.
    Also I changed the link to your site.
    Thank you very much

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top