Unittesting PL/SQL (ODTUG 2006)

For me, one of the highlights of this year’s ODTUG conference was the presentation of Steven Feuerstein about PL/SQL unittesting, “Six simple steps to unit testing happiness“.
While unittesting is (more or less) a standard practice in the java world, it is less so in the database world and even more surprising, in many cases the usefullness is still the subject of hot and fierce debates. Feuerstein seems to be the lone crusader in advocating and practicing pl/sql unittesting. This was again supported by the fact that within the crowd of more than 100 attendees, only a few of them used a formal, automatic, way of testing PL/SQL code.

The first part of the presentation aimed at creating awareness for the necessity of testing your pl/sql code to prevent writing buggy code, because buggy code is embarrassing, expensive and deadly. I particularly liked the phrase: “Imagine that you write code that has to be maintained by your own children”.

Feuerstein proved to be a strong advocate of test driven development for which he has two simple but effective reasonings:
– If you write your program before you define your tests, how do you know you when you’re done?
– If you write your tests afterward, you are likely to prejudice your tests to show “success.”

So, PLSQL/development and testing can be practiced with the following six step approach:
1. Describe fully the required functionality of the program
2. Elaborate the test cases for the program.
3. Define the header of the program (name, parameter list, return value).
4. Build test code that implements all test cases.
5. Write the program unit.
6. Test, debug, fix, test, debug, fix, test, debug….
Then…repeat steps 3-6 for each enhancement and bug report

In the the second half of his presentation he demonstrated the importance of a framework for writing unittests. His new product, Qute (Quick Unit Test Engine), offers a comprehensive, visual, way of writing these tests, instead of hand-coding all the tests with utPL/SQL. Since I have a lot of experience with utPL/SQL I am very eager to try and use Qute.

So let’s stop discussing difficulties with database unittesting and let’s start testing…