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. Sohil 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

Running out of PGA memory with MULTISET ops? Watch out for DISTINCT!

A PL/SQL team inside Oracle made excellent use of nested tables and MULTISET operators in SQL, blending data in tables with procedurally-generated datasets (nested tables).  All was going well when they hit the dreaded: ORA-04030: out of process memory when trying to allocate 2032 bytes  They asked for my help.  The error occurred on this SELECT: SELECT  *    FROM header_tab trx    WHERE (generated_ntab1 SUBMULTISET OF trx.column_ntab)       AND ((trx.column_ntab MULTISET             EXCEPT DISTINCT generated_ntab2) IS EMPTY) The problem is clearly related to the use of those nested tables. Now, there was clearly sufficient PGA for the nested tables themselves. So the problem was in executing the MULTISET-related functionality. We talked for a bit about dropping the use of nested tables and instead doing everything in SQL, to avoid the PGA error. That would, however require lots of wo...
4 comments
Read more

How to Pick the Limit for BULK COLLECT

This question rolled into my In Box today: In the case of using the LIMIT clause of BULK COLLECT, how do we decide what value to use for the limit? First I give the quick answer, then I provide support for that answer Quick Answer Start with 100. That's the default (and only) setting for cursor FOR loop optimizations. It offers a sweet spot of improved performance over row-by-row and not-too-much PGA memory consumption. Test to see if that's fast enough (likely will be for many cases). If not, try higher values until you reach the performance level you need - and you are not consuming too much PGA memory.  Don't hard-code the limit value: make it a parameter to your subprogram or a constant in a package specification. Don't put anything in the collection you don't need. [from Giulio Dottorini] Remember: each session that runs this code will use that amount of memory. Background When you use BULK COLLECT, you retrieve more than row with each fetch, ...
4 comments
Read more

PL/SQL 101: Three ways to get error message/stack in PL/SQL

The PL/SQL Challenge quiz for 10 September - 16 September 2016 explored the different ways you can obtain the error message / stack in PL/SQL. Note: an error stack is a sequence of multiple error messages that can occur when an exception is propagated and re-raised through several layers of nested blocks. The three ways are: SQLERRM - The original, traditional and (oddly enough) not currently recommended function to get the current error message. Not recommended because the next two options avoid a problem which you are unlikely  to run into: the error stack will be truncated at 512 bytes, and you might lose some error information. DBMS_UTILITY.FORMAT_ERROR_STACK - Returns the error message / stack, and will not truncate your string like SQLERRM will. UTL_CALL_STACK API - Added in Oracle Database 12c, the UTL_CALL_STACK package offers a comprehensive API into the execution call stack, the error stack and the error backtrace.  Note: check out this LiveSQL script if...
5 comments
Read more