OracleBlog

Gary pointed me to a recent AskTom thread that contained a list of PL/SQL Coding Practises. The ensuing discussion added a few more, and I'd like to throw in a few of my own as well. I also think most of these tips are useful for other languages, but this is first and foremost an Oracle developer's blog.

Here they are, with the order and wording slightly revised in some cases (removed the biblical undertones for politically correct reasons), and my comments after each one. Each of these could be a separate blog on their own, but I restrained myself to only brief comments.

Design (Pre-Coding):

1. Ask Why

A popular philosophy in the Oracle blogging community, and championed by Tom Kyte, is the notion of asking why. Why is this code being written, why is it being done the way that it is, etc.

From Tom Kyte's Blog: http://tkyte.blogspot.com/2005/05/why.html

2. Gather requirements thoroughly

Based on the experiences of many, gather all the requirements and do not begin anything until all parties have agreed on what the end result will have to be in order to be considered successful.

3. Design, including for scalability and security, first.

With the exception of quasi-prototypes and small proofs of concept, no coding should begin until it has been designed. This is the time to think about scalability and security. Those aren't issues to tackle after the fact.

4. Set up proper testing/development/debugging environment

I find that developers are far more effective if they have a reliable, accurate and convenient environment for testing and debugging. Also, it is usually better to set that up first, rather than patching something together on the fly.

5. Use source control

"Consider source control non-optional, and a factor on which your job depends." - a former director of mine. That simplifies your decision, doesn't it?

6. Choose the right tools

Notepad and SQL*Plus might work for you now, but for a larger project you might want to consider a different IDE. You'll also want to look at source control systems, and code libraries.

7. Write test cases before coding

It seems like many people agree on this rule, but consider it a fantasy because how often do you see this done in practise? Ideally you would do this right after #2 (gather requirements thoroughly). That way you can say "when these tests pass, you are done."

Coding:

8. Check and handle errors

Up front, decide a common way of handling errors in the EXCEPTIONS block. You can figure out what works best, customer error types, an error table, an error file, whatever works as long as you are checking and handling all errors.

For starters, check Chapter 7 of the PL/SQL User's Guide and Reference: Handling PL/SQL Errors:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624.pdf

9. Comment your code

This one resulted in a lot of debate on the thread. No, commenting your code is not a substitute for making your code readable, but then again, neither is making your code readable a substitute for commenting. Indeed, I agree with the one suggestion that you should write your comments BEFORE you write the code!

10. Use proper indexes

11. Maximise SQL and minimise PL/SQL

There are books written on the efficiency gain of preferring SQL over PL/SQL.

12. Instrument your code for debugging

Choose your favourite method: debug statements judiciously places throughout your code, or perhaps using DBMS_PROFILE (that is definitely a topic I'll do soon), or something else entirely. As long as you have a way to troubleshoot bugs and performance issues later on.

13. Make use of bulk processing

14. Minimise client code and maximise server code.

Generally servers are more powerful and built for this type of work. You also want to minimise trips back and forth to the server.

15. Use bind variables, not concatenation

Not just for performance reasons, but also for security (thwart SQL injection). I think Tom Kyte made his living off this topic for awhile.

I blogged about bind variables once: http://thinkoracle.blogspot.com/2005/06/bind-variables-in-plsql.html

16. Think in sets.

17. Use procedures/functions to name and modularize your code for reuse

Don't just create one big massive function that does many, specific things. Best to break it out into specific tasks, which can be optimized and reused. Also makes you think more about your detailed design.

18. Use unique and meaningful names

Unique names can be found easier in the code with "Find". Meaningful names make your code easier to understand. If you can't think of a meaningful name for a procedure/variable, maybe you don't have a clear idea on its purpose and you need to re-think your design.

Coding and Afterwards:

19. Stress test your code with significant data, and benchmark it

Doesn't matter how much time you spent thinking about performance and bottlenecks during the design and implementation, you might still have missed a few things.

20. Perform a code review with your peers.

Code reviews are good to find mistakes, but also for knowledge transfer. I also think you take more pride in your work when you have the opportunity to share it, rather than just hide in a cubicle as an anonymous coder.

Comments are welcome, but I also encourage you to visit the thread and follow up to the wider audience there:
http://asktom.oracle.com/pls/ask/f?p=4950:8:18231831513891656743::NO::F4950_P8_DISPLAYID,
F4950_P8_CRITERIA:51960184066540

Steven Feuerstein is also one of my favourite champions on good PL/SQL programming. Here is a previous blog on one of his articles on refactoring (the link to which is within):
http://thinkoracle.blogspot.com/2005/05/steven-feuerstein-on-refactoring.html