Thursday, October 22, 2015

Most important PL/SQL coding standards?

Received this request today via email:
I was at the MOUG Fall Conference in Chicago a few weeks ago and enjoyed your presentation on the result cache. It’s already paying dividends for us. Thanks for coming and sharing. I have a question for you, and maybe you’ve already written about this and can point me toward an article or blog post. We will be revising our coding standards, which are rather loose and largely ignored, and I want to try to promote those that will give us the most benefit. What is your top ten list of the most important coding standards to implement? Thanks for your time, and I hope to see you at OOW. It will be my first trip there.
And I replied:
I love these kinds of requests, because it gives me an opportunity to take a fresh look and publish something on my blog. :-) I don’t think I will be able to get back to you until after OOW, hope that works OK. Please do come up and say hi if you see me!
And then I thought: wait a minute, let's ask all my fellow Oracle Database developers "out there", see what all of you think. 

So here I am, there you are - and I'd love to hear from you:

What do you think are the most important coding standards for PL/SQL developers to follow?

By the way, check out some existing, published standards and frameworks here

Nov 6 update: it's been a busy post-OOW week, so I haven't been able to formulate my complete answer yet. I like lots of the ideas submitted in the comments. But I have come up with nine keywords to drive my "most important." They are:


1. MAXSQL - maximize use of SQL first and foremost
2. SPOD  - single point of definition
3. TRACE - production-available application-level tracing
4. LOG - consistent, encapsulated error logging
5. BULK - avoid row by row
6. OBVIOUS - make your code tell its own story, comment when it can't
7. NESTPROG - use nested subprograms
8. DECLARE - use declarative features of language
9. WARN - use compile-time warnings

November 12 2015: I have published an 8 minute video explaining these Top Nine. Hope you like it!

15 comments:

  1. Thanks Steven, this is really valuable and under-rated information contained in that link. One of my top recommendations, is implementing proper exception handling as a standard in one's organisation. Decide on a strategy for logging and reporting warning/errors to your support team. I would advise creating a package which captures log and session information, but using the DBMS_UTILITY.format_error_backtrace utility which you described in http://www.oracle.com/technetwork/issue-archive/2011/11-jan/o25plsql-093886.html is key. To me this is invaluable and wish it was a standard everywhere!

    ReplyDelete
  2. Consistency, consistency, consistency :)

    ReplyDelete
  3. * named parameters in procedure/function calls
    * use aliases everywhere in SQL
    * spaces (not tabs)
    * liberal use of blank lines
    * one statement per line
    * coding standards may be broken in rare cases where it makes sense

    ReplyDelete
  4. To me the most important is the error handling and logging. It has to be consistent throughout the application and easy to use.
    Because of your post I published a simplified example of how we manage Named Exception at Live SQL:
    https://livesql.oracle.com/apex/livesql/file/content_CCDPAOR8IXKYVUT9YHMCZ7W3U.html

    Many coding standards tell us a lot about indentation, upper/lower case, naming of objects etc. That's ok but I would expect rules when to modularise code, how many parameters you define in a procedure before you use a rowtype to pass them ...
    I mostly have to maintain existing code and then the only rule is "When in Rom do as the Romans do" - write your code as it has been done by your precedessors.

    And never forget LuTse: "Rules are there to make you think before you break them"

    Regards
    Marcus

    ReplyDelete
  5. No dynamic SQL, otherwise why use PL/SQL

    ReplyDelete
    Replies
    1. It can certainly be misused (like any language feature), but dynamic SQL is an important part of PL/SQL, so I wouldn't rule it out unconditionally.

      Delete
    2. Ah, OK, I guess I should just read Kevan's entry plainly. He is saying "Do not do dynamic SQL." as a coding standard. Interesting.

      I look at it this way: dynamic SQL should only be used when the alternative (static or embedded SQL) is not possible. THAT is the coding standard.

      If you do not have all the information needed at compile-time to construct your SQL statement or PL/SQL block, you go with EXECUTE IMMEDIATE (or in the even more rare case, DBMS_SQL).

      And when you do use dynamic SQL, you are aware of key issues (SQL injection, performance related to binding, maintainability, etc.) and factor them into your implementation.

      Delete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Steven,

    Thanks for the video. You've answered my question and given us lots to talk over. Now, do you have any advice on getting those resistant to change, crusty, old, developers to buy in to change?

    But that's a whole other issue.

    Sound like another therapy session?

    -Greg

    ReplyDelete
  8. In a word, incentives. OK, two words: incentives and fun. More later.

    ReplyDelete
  9. Later is now. For my thoughts on getting crusties to de-crustify, check out http://stevenfeuersteinonplsql.blogspot.com/2015/11/programmers-are-humans-too-how-to-get.html

    ReplyDelete
  10. Steven,

    I learnt a lot from you, your articles in Oracle Magazine and The PL/SQL Challenge.

    I agree with most of the suggestions given in comments and the best practices you have suggested earlier.

    My best practice are below:

    * Always use Exception Block within each block, function, procedure
    * Send email to your development team for any error which occurs in Production
    * Give top priority to error email
    * Make sure you will never get any error email :-)
    * Write reusable procedures/functions and SQL (If you use APEX you can use List of values)
    * Avoid using Triggers except audit
    * Make sure you add below 5 columns to important tables for Audit.
    1. Created by User
    2. Creation Date
    3. Updated by User
    4. Update Date
    5. User Agent
    6. IP Address
    * Avoid using Stateful Package means do not use package level variables/constants and instead create a DB table which stores all constants. This will avoid causing "ORA-06508: PL/SQL: could not find program unit being called" if you need to do a minor change in package. This could heavily affect where the application has thousands of users.

    There may be many other points but I think these are the top ones according to my knowledge.

    Regards,

    Sohil Bhavsar.

    ReplyDelete
  11. Always use bind variables. Since in PL/SQL, bind variables don't get a special syntax like "?" or ":varname", I name them so that it is obvious that these are variables - usually prefixing with "p_".
    Users never connect as the owner of objects. In fact, developers never do except in a development database.

    ReplyDelete
    Replies
    1. Which works perfectly until one day, someone adds a column named "p_"+something to a table... :)

      Delete
    2. Thou shalt not create columns with the prefix "p_". Solve a problem with one standard by creating another standard. :)

      Delete