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. NickOctober 22, 2015 at 11:51 AM

    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. Jeffrey KempOctober 25, 2015 at 5:54 PM

    Consistency, consistency, consistency :)

    ReplyDelete
  3. Jeffrey KempOctober 25, 2015 at 5:58 PM

    * 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. MarcusOctober 25, 2015 at 11:16 PM

    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. Kevan GellingNovember 5, 2015 at 7:43 AM

    No dynamic SQL, otherwise why use PL/SQL

    ReplyDelete
    Replies
    1. Jeffrey KempNovember 6, 2015 at 6:08 AM

      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. Steven FeuersteinNovember 6, 2015 at 6:15 AM

      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. Steven FeuersteinNovember 5, 2015 at 8:09 AM

    This comment has been removed by the author.

    ReplyDelete
  7. UnknownNovember 12, 2015 at 7:43 PM

    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. Steven FeuersteinNovember 12, 2015 at 7:45 PM

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

    ReplyDelete
  9. Steven FeuersteinNovember 19, 2015 at 10:36 AM

    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. Sohilkumar BhavsarDecember 1, 2015 at 11:30 AM

    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. John FlackDecember 28, 2015 at 5:39 AM

    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. Jeffrey KempDecember 28, 2015 at 6:14 AM

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

      Delete
    2. John FlackFebruary 3, 2016 at 1:55 PM

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

      Delete
  12. G MoneyNovember 27, 2017 at 11:12 AM

    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. Steven FeuersteinNovember 27, 2017 at 12:39 PM

      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

Three tips for getting started right with Oracle Database development

By "Oracle Database development", I mean, more or less, writing SQL and PL/SQL. I assume in this post that you have access to Oracle Database (which you can get via Cloud services, Docker , GitHub and OTN ). A. Use a powerful IDE, designed with database programming in mind. There are lots of editors out there, and many IDEs that work with Oracle Database. Sure, you could use Notepad, but OMG the productivity loss. You could also use a popular editor like Sublime, and then it get it working with Oracle. I suggest, however, that you  download  and install Oracle's own own, free, powerful IDE: SQL Developer . If you like to complement your graphical IDE with a command line tool (or OMG if you actually prefer  a command line tool to a graphical interface), you should also check out the relatively new and generating-lots-of-excitement SQLcl . B. Enable compile-time warnings and PL/Scope. The database has tons of useful functionality burned right into it, ready for...
8 comments
Read more

Why DBMS_OUTPUT.PUT_LINE should not be in your application code

A database developer recently came across my  Bulletproof PL/SQL  presentation, which includes this slide. That first item in the list caught his attention: Never put calls to DBMS_OUTPUT.PUT_LINE in your application code. So he sent me an email asking why I would say that. Well, I suppose that is the problem with publishing slide decks. All the explanatory verbiage is missing. I suppose maybe I should do a video. :-) But in the meantime, allow me to explain. First, what does DBMS_OUTPUT.PUT_LINE do? It writes text out to a buffer, and when your current PL/SQL block terminates, the buffer is displayed on your screen. [Note: there can be more to it than that. For example, you could in your own code call DBMS_OUTPUT.GET_LINE(S) to get the contents of the buffer and do something with it, but I will keep things simple right now.] Second, if I am telling you not to use this built-in, how could text from your program be displayed on your screen? Not without a lot o...
4 comments
Read more

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts

Sten Vesterli published a very thought-provoking post on his blog: Please stop reading this post, and read that one. When you are done, come on back here for my thoughts on Sten's thoughts. OK. You read it. Here we go. First, thanks, Sten, for being such an interesting, wise, sometimes provocative voice in our community. Next, Sten writes: Now, on the one hand, I certainly agree that the vast majority of young developers are currently caught up in the modern version of a Gold Rush, which is: "Build an app using JavaScript, pay no attention to that database behind the curtain." But I can assure you that I still do meet young PL/SQL programmers, regularly, when I am at conferences and doing onsite presentations at companies. So, young person who writes PL/SQL: do not be afraid! You are not alone! And you are super-smart to have made the choice you did. :-) Next, Sten offers this advice to managers: I agree that PL/SQL is a "spec...
46 comments
Read more