Skip to main content

Checklist Driven Development: TDD on the cheap - VERY cheap

I've built (or helped build) two unit testing frameworks for PL/SQL over the years: utPLSQL (open source) and Code Tester for Oracle (Dell). I pushed long and hard with my audiences to follow test driven development, to build automated regression tests, etc.

The peak of my TDD/unit testing drive was represented by my "Six Simple Steps to Unit Testing Happiness." I had fun doing these presentations, and my audiences laughed at how I made fun of we all tested (or rather did not test). And I am sure that I did have some impact on behavior....but....

While I am still convinced of their value, I spend far less time promoting these ideas these days, mostly for the following three reasons:

1. I hate hypocrites (who doesn't?). And the hypocrite I detest more than any other? Me. Bottom line is that I did not / do not use either of those tools (nor SQL Developer's integrated unit testing) when I write code. Why? All the usual excuses I pooh-poohed in my talks: "Not enough time" "Hard to set up and tear down database state" and many more. At my advanced stage of life (I turn 57 in September), I am less tolerant of my own hypocrisies.

2. I don't believe it is possible to dramatically change the behavior of humans, not through lecturing them anyway. People who have been avoiding testing for years or decades (or, to be a bit kinder, unable to test due to lack of time and resources) are not likely to start. Barriers to entry are too high, by which I mean:

3. It's still too hard. utPLSQL requires writing too much code. Code Tester for Oracle and SQL Developer both generate a bunch of code, but defining a stable dataset for testing and refresh that dataset remain very off-putting tasks.

I try, these days, to live in the real world, to accept things as they are, and work from there. Doing full-blown test driven development (TDD) simply is not going to happen widely in the Oracle database programming world.

That doesn't mean, however, that we should just throw up our hands and say "Oh well, I'd love to 'do things right' but I just can't" and then fall back into our lovely "rapid application development": an absolute minimum of process, standards and collaboration, and the maximum possible number of short-cuts. (which we do all too eagerly, if we are really honest with ourselves)

No, no, no, we should not do that!

So here's where I get to the point of this post: I'd like to offer a simple, practical, eminently do-able "poor man's" version of TDD, one that anyone can do, one that doesn't take a lot of time to do, one that can give you some very quick positive feedback, thereby encouraging you to do it more and more:

Checklist Driven Development (ChLDD)

[Humbling note: we live in a post-modern world. So of course I am not the first to come up with this term and associated meaning. Check out Dan DeMeyere's blog. But hey! I am still blogging. He stopped "back" in 2014. So there.]

Assuming you are reasonably competent and want to write high quality code, I suggest one of your (our) biggest challenges is simply to remember what it is we need to do, and what we need to check for, when producing a new program.

So why a checklist?

Many surgeons - whose work is way more complicated and life-critical than ours - have discovered in the past few years that using a simple (almost ludicrously simple) checklist before starting surgery can have an enormous impact on success. Their checklist might feature questions like:

  • Check the patient's identity.
  • Confirm the limb you are supposed to remove. 
I know, I know. Seems crazy obvious, right? But it works, it really works. Which is a sad commentary on the medical system, sure. The main thing, however, is to focus on getting better. 

So....why wouldn't it work for developers, too? Here is my suggestion for "Checklist Driven Development" (specifically as it applies to a substitute for actual unit testing):

Before you are about to start writing a new program (or upgrading an existing one), ask yourself this question:

"How will I know when I am done?"

After all, you are going to start thinking and typing and pressing buttons, quite furiously, and then at some point, you are going to stop and say to yourself: "Good (enough). I am done. Now I can work on the next program."

How will you know when it's safe to stop, and move on?

All too often, the answer is "I'll know it when I see it." or "When I am done testing it." But so few us actually have any kind of test plan, so testing really comes down to "Try a few of the most obvious - to me - scenarios and that should be good enough."

Or if we do have a test plan, it is written after the program has been written. And that's a really, really bad idea. Here's why:

Ever try to proofread your own writing? It's nearly impossible to catch typos and grammatical errors. Since you wrote it, you know it too well, and your eyes/mind just skip over errors, automatically adjusting to what you expect to be there, rather than reading what actually is there. 

That's why you will hear the recommendation to proofread by reading the words in reverse. That way your brain can't glom onto the meaning. It has to process each word individually.

Kind of hard to do with code and, anyway, the compiler will take care of most typos.

But if you build your test plan after you "finish" writing the program, your mind will subconsciously focus on test cases for the parts of the program in which you have the most confidence. And that same mind of yours will veer away from the parts that you pretty well know are sub-par and likely to be socked full of bugs (primary rationalization here: "(Almost) no one's going to use those features, anyway." Yeah, uh huh.).

Don't fall for that simple trap!

Instead, build the checklist first

Right before you are going to start that next program, ask yourself: "What does this program have to be able to do in order for me to report to my manager that I am done?" 

Then open up an editor and put it into a document. It doesn't have to be anything fancy. Here's what I (pledge to) use (from now on):

This way, your list will not be affected (corrupted) by the fact that you only have five minutes to test when all is said and done. It will not be perverted by subconscious bias. You can, with a light heart and optimistic outlook, simply list the things the problem should do.

Here's an example of a specific checklist:

Is ChLDD an adequate substitute for real testing, unit or otherwise? Absolutely not. 

Is it better than launching oneself full speed into writing a program before you've thought through what is needed and how you are going to build it? I think so.

Could this be improved? Sure, you could add a section for defining behavior when errors occur, for example. But at least for this post and for my initial use, I want to keep it dead simple. Which means I can fill it out quickly and get immediate value for the time spent.

Let me know if you try it, and what your experience is like.


  1. Hi Stephen.
    Even with ChLDD, you still need/should do some check before you tick the box - DOES.
    How will you do that? I bet as simple as possible!

    Why not then use the "as simple as possible" check as a unit test?
    The benefit would be that at least you leave something to go back to and verify that "stuff" is still working as it was supposed to be.
    The Software is no linger build to last, it is build to change, and test are to make the change safe.

    There is the catch that I also fall in very often - rushing into implementation prior to writing a test case.However, there is always a question of what really the program should be doing.
    The Unit Tests are to document the requirements in an executable form, so that the next time someone comes to change the code, looking at tests, he/she knows what the code is currently doing.
    This is of the particular value when dealing with large and or complex systems.

    Ian Cooper made a great talk about "TDD - where did it all go wrong".

    What are your thoughts on what he is saying in the interview?

    Here is the full presentation from the conference:

  2. Hi Steven,

    first let me thank you for programming utplsql.
    I use it now for over 7 years. And from the beginning i was trying to optimize the way how to define unit-tests.
    I was really kind of shocked, as i read, that you dont unit-test when you code. Really, i dont believe.
    I can not live without unit-tests. This is the first thing i do when starting to code.

    The most important thing: To define a unit-test, it must be very easy. The Developer needs to LOVE unit-testing.
    In the "best case", the developer does the unit-test together with the business specialists.
    And what is the tool everybody understands, especially "business-people": Its Excel.
    Excel is the natural-born-unit-testing-tool for plsql. Believe me.
    You can sit together with everybody and discussing the scenarios (or send per email). You even can get a business-approval.
    I did some excel-macros in the last years to make life as easy as possible.
    import table-structures, import testdata, validate testdata format, generate utplsql, execute it, show result in html...
    I dont know the other tools (Code-Tester...), but i guess
    they do the same, but: is the tool as easy and powerful as Excel?
    We are using many features of Excel when defining tests. Formatting, Colours, formulas, comments... what you want.
    Because with high-complex ETL jobs it is so important to have it good documented.

    And at the end we only press button "test" inside excel, and we see if everything works.
    In the background a utplsql-package is generated, and executed.

    In my life-demos, i do pair-developing with people from the audience,
    (i define unit test, audience is programming against them, 3 cycles of going red and green), and in a result
    we put the generated utplsql package into svn and hudson for continuous integration. waiting one minute, and then
    getting automated email that tests are succesful now.

    We are a SCRUM-Team of 10 DWH-developers. It is so awesome seeing how this team is performing.
    We have now problems to get enough work from business(!!) (and we are able to refactor whatever we want without pain),
    and we are running and supporting our DWH-Software now in 3 countries.

    We are running 100.000 Unit-tests every 20 minutes.


    1. Well done Stefan, Really impressed. Can you give us a demo on how you do all this, may be through video or a blog ?

    2. Maybe i could do the same life demo just via webex or something similar.. hm. thinking about.

    3. Stefan, think about it and let me know (either here or via email: steven dot feuerstein at oracle dot com). I would be happy to host you for a conversation and demonstration.

    4. Hi Steven,

      wow thats pretty cool. Steven Feuerstein wants a live-demonstration.
      For sure, i will give.
      I would need a little bit time to prepare an adapted demonstration. Maybe in octobre/november?

      This tool makes developer`s life so easy. (especially pl/sql-developers life, but even i used it in the past with informatica and talend as well, and that poor ones need it even more)

      What i am most proud of is, that i really evangelized a team to change their way of coding. The resulting quality of the unit-tests was so good, that they changed their transition process and the testers in many cases only do unit-test reviews.

      If someone else is interested please contact me via email: stefan dot poschenrieder at gmx dot de.


    5. "What i am most proud of is, that i really evangelized a team to change their way of coding. The resulting quality of the unit-tests was so good, that they changed their transition process and the testers in many cases only do unit-test reviews."

      Nice work, Stefan. That is not easy to do. I am very impressed. Perhaps you could work your magic on me, too? :-)

    6. Stefan, hello.

      Let me invite you to the next chapter of utPLSQL. Currently the community works hard on brand new version3 of the framework and we will greatly appreciate your opinion about what's already done and what should be improved.

    7. Stefan, I seem to have somehow lost your email and would like to get in touch. Could you please follow up with me at steven dot feuerstein at oracle dot com?

  3. Stefan, thanks for sharing your experience. Excel! Who would have thought? This is a very exciting note, it's so great to see utPLSQL going strong. Are you involved in the project itself (just moved to Github). I wonder if there are any Excel-related resources (integration with utPLSQL) you could share via the project so others might take advantage of all the great infrastructure you have built.

  4. Stefan, thanks for the live tutorial you gave me.

    Your Excel based approach makes it a lot easier for both end users and developers to be involved in the Unit Testing process!

    Thanks again!


Post a Comment

Popular posts from this blog

Get rid of mutating table trigger errors with the compound trigger

When something mutates, it is changing. Something that is changing is hard to analyze and to quantify. A mutating table error (ORA-04091) occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement). In particular, this error occurs when a row-level trigger attempts to read or write the table from which the trigger was fired. Fortunately, the same restriction does not apply in statement-level triggers. In this post, I demonstrate the kind of scenario that will result in an ORA-04091 errors. I then show the "traditional" solution, using a collection defined in a package. Then I demonstrate how to use the compound trigger, added in Oracle Database 11g Release1,  to solve the problem much more simply. All the code shown in this example may be found in this LiveSQL script . How to Get a Mutating Table Error I need to implement this rule on my employees table: Your new salary cannot be mo

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,

Quick Guide to User-Defined Types in Oracle PL/SQL

A Twitter follower recently asked for more information on user-defined types in the PL/SQL language, and I figured the best way to answer is to offer up this blog post. PL/SQL is a strongly-typed language . Before you can work with a variable or constant, it must be declared with a type (yes, PL/SQL also supports lots of implicit conversions from one type to another, but still, everything must be declared with a type). PL/SQL offers a wide array of pre-defined data types , both in the language natively (such as VARCHAR2, PLS_INTEGER, BOOLEAN, etc.) and in a variety of supplied packages (e.g., the NUMBER_TABLE collection type in the DBMS_SQL package). Data types in PL/SQL can be scalars, such as strings and numbers, or composite (consisting of one or more scalars), such as record types, collection types and object types. You can't really declare your own "user-defined" scalars, though you can define subtypes  from those scalars, which can be very helpful from the p