PL/SQL do's and don't's: Five questions with Steven Feuerstein

We asked Steven Feuerstein -- a leading expert on Oracle PL/SQL programming who has written 10 books on the subject -- five questions based on his Collaborate '07 presentation titled "Ten things you should never in PL/SQL." His answers are below.

Is it okay for programmers to ask for help?

Absolutely not! If you ask for help, you are showing weakness. The flock of healthy programmers will then isolate you, so that the nearest predator eats you and not them.

Oh, wait. Programmers aren't birds. We don't have to worry about predators, only about writing high-quality applications that meet our users' needs.

So, in this case, I take back everything I said. Yes, it is absolutely OK for programmers to ask for help. In fact, if you don't know something and you hide that ignorance, it is as though you are stuffing your program full of bugs right in that area of ignorance.

It is extremely important, in fact, for IT managers and team leads to support a culture in which it is OK to admit you don't know something, and to ask for help. The very best way to do this is for the senior programmer, or at the least the person whom everyone respects, to take the lead, to seek out opportunities to ask others for help.

Once the team sees that even the local "know-it-all" sometimes needs help, it will be easier for them to ask for help.

Should we always trust the gurus?

Not if they are older than 30. In case anyone is wondering, I am 29. Have been for years.

Sure, trust the gurus. Just don't believe anything they say, especially when it comes to performance.

Here's the point: just because Steven, "one of the world's leading experts on PL/SQL," has had a certain experience with some feature of PL/SQL doesn't mean that you will have the same experience on your version of Oracle, in that operating system, etc.

There's no faith involved in programming. Either it works or it doesn't. So any so-called expert who is not stuffed too full of him- or herself should be expected to provide scripts to back up what they say, scripts that you can run yourself to verify any claims made.

I try to do this, and all of my scripts are available at Oracle PL/SQL Programming, along with all of my seminar materials. You can download and use any of it in your own internal corporate environment. Teach classes with my materials! Use my code in your applications! Run my numerous *tst to test my recommendations.

What are the benefits of code review?

Code review -- letting other people look at and critique your code -- can be scary, unless the right culture exists in your group. But it is oh, so important to do this. Really and truly, no code should be put into production unless some other human being has looked at it. There are two many ways that a person, sucked deep into the problem space of their requirements and programs, can mess up without even realizing.

Even knowing that possibly someone else might someday look at your code can dramatically change the quality and content of the code you write.

Again, the best way to put code review in motion is for the most experienced developer to take the initiative and ask others to meet with her to look over her latest work of art, I mean software. Then the message is clear: it is OK to critique her stuff, so surely it is OK to look at my code and give me feedback (find areas to improve).

Finally, you should also look into automated code review options. Toad's Code Xpert feature, for example, has incorporated all the recommendations from my Oracle PL/SQL Best Practices text, and many more rules beyond that. With the press of a button, Code Xpert will analyze your program and give you line-by-line feedback on how you can improve your code.

Should programmers hide the details of their code?

I just talked about how programmers should invite others to look at their code, so it is strange to now say: "Yes, you should hide the details of your code!" But we are really talking about a different issue here.

"Information hiding," as it is more commonly known, is a critical concept in writing high-quality software. The basic idea is that as we write our applications, we don't want to get bogged down in unnecessary details. We need to stay focused on the task at hand, the requirements being implemented in this program. And then within that program, we are still dealing with lots of complexity.

If you are not careful about how you write your code, you will end up with big blobs of spaghetti code, impossible to read, very difficult to debug, enhance and manage.

A much better approach to writing code is to encapsulate, to put chunks of complex logic behind a name (that is, a procedure or function call). You then call that subprogram in your executable section, rather than exposing all the details. In other words, you hide the details behind a program call.

I have gotten fairly complusive about such hiding. My rule of thumb (which I strongly urge in my trainings as one of my four top tips for programmers) is that no executable section shall have more than 50 lines of code. I usually aim for more like 20 to 30 lines.

"How is this possible?" you must be asking. Mainly by building reusable programs, but also by leveraging local or nested modules. That is, right inside the declaration section of my block (anonymous or named), I can declare other subprograms that can be called only within that block.

My feeling is the more you hide the better. Sure, you could go overboard, but I don't think that's likely to be a problem for too many developers.

What's the best way to deal with the problem of buggy software?

Get rid of the bugs!

This is my current sub-obsession, actually (within my broader, general obsession with PL/SQL): improving the quality and quantity of testing of PL/SQL programs.

This is what I think programmers should do to reduce the number of bugs in their code:

• Standardize development as much as possible. Use standard components for low-level aspects of the code base (error management, SQL access, etc.).
• Use top-down design to keep the code clean and eminently transparent in meaning (see previous Q&A).
• Think about testing and even implement your test code before you implement your program. The fancy term for this is "test-driven development."
• Automate the unit testing process as much as possible. Without automation, you don't stand a chance to erase most of the bugs from your code. In the world of PL/SQL, options for automated testing are limited, but also very useful. You can choose between utPLSQL and Quest Code Tester for Oracle.

utPLSQL is an open source framework modeled on Junit (I wrote the original version back in 1999). It offers some automation, but still requires that you write the test code yourself.

Quest Code Tester is a new (and new kind of) testing tool that I have been helping build for the last two years. It offers a much higher level of automation, since it allows you to simply describe your tests in a UI, and then it generates the test code from your descriptions. Pretty cool stuff, if I do say myself, and I strongly encourage any PL/SQL developer interested in improving their testing to check it out. Trial, commercial and freeware versions are available.