Skip to main content

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!

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
  12. This link no longer works...

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

    http://www.toadworld.com/platforms/oracle/w/wiki/8245.plsql-standards

    Do you know if that got moved somewhere?

    ReplyDelete
    Replies
    1. Thanks for pointing that out. I will change the link to:

      http://stevenfeuersteinonplsql.blogspot.com/2016/10/naming-conventions-for-oracle-database.html

      Delete

Post a Comment

Popular posts from this blog

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p

The differences between deterministic and result cache features

 EVERY once in a while, a developer gets in touch with a question like this: I am confused about the exact difference between deterministic and result_cache. Do they have different application use cases? I have used deterministic feature in many functions which retrieve data from some lookup tables. Is it essential to replace these 'deterministic' key words with 'result_cache'?  So I thought I'd write a post about the differences between these two features. But first, let's make sure we all understand what it means for a function to be  deterministic. From Wikipedia : In computer science, a deterministic algorithm is an algorithm which, given a particular input, will always produce the same output, with the underlying machine always passing through the same sequence of states.  Another way of putting this is that a deterministic subprogram (procedure or function) has no side-effects. If you pass a certain set of arguments for the parameters, you will always get

My two favorite APEX 5 features: Regional Display Selector and Cards

We (the over-sized development team for the PL/SQL Challenge - myself and my son, Eli) have been busy creating a new website on top of the PLCH platform (tables and packages): The Oracle Dev Gym! In a few short months (and just a part time involvement by yours truly), we have leveraged Oracle Application Express 5 to create what I think is an elegant, easy-to-use site that our users will absolutely love.  We plan to initially make the Dev Gym available only for current users of PL/SQL Challenge, so we can get feedback from our loyal user base. We will make the necessary adjustments and then offer it for general availability later this year. Anyway, more on that as the date approaches (the date being June 27, the APEX Open Mic Night at Kscope16 , where I will present it to a packed room of APEX experts). What I want to talk about today are two features of APEX that are making me so happy these days: Regional Display Selector and Cards. Regional Display Sel