I'm thinking a lot about unit testing PL/SQL this week. I'm fortunate to have somebody assigned to help write the test plan for the project I'm working on, but I hope to make the time to take control myself and do a thorough job. Unfortunately, I've never been able to get where I can write tests before coding, and switching from Perl to PL/SQL certainly slowed down my development in that regard. One day I will write my tests first, and I will probably be using utPLSQL, but I still have some things to learn first.
I've written a couple of private test routines just to convince myself the code does something (note that I did not say "works"). These are really SQL*Plus scripts, but obviously I want to upgrade to something better. One thing I noticed is that in order to test the routines in my package, I had to create a "promiscuous" package specification that exposes every single routine so I can test it. I don't like doing this; maintaining two parallel package specifications is obviously a nightmare.
I'm thinking that it would be nice to have a built-in routine called "test" in every package to do my testing for me. The advantage of doing this is that that routine can see everything in the package and test it. I'm envisioning a standard interface for this routine, something that would run sort of like a Perl testing suite, and make calls to a testing package like utPLSQL or a smaller alternative I am dreaming up called T. (T.ok() is a pretty short name for a procedure, no? ;) ) The disadvantage would be still having that routine exposed in production code, or else having to change the code at build time. I don't personally consider that to be too bad of a disadvantage, but it would be nice to have some kind of conditional compilation feature to eliminate it. Actually, given that conditional compilation reminds me of brain damage, maybe it wouldn't beso nice.
Testing here is better than at my last position, but still not as official and experienced as I would like. My experience with Perl, modules, and the Perl community has really prepared me. I owe a great deal of thanks to chromatic, schwern, and petdance for educating me about testing, showing me how important it is, and how it should work, and how it can benefit me. Thanks so much, guys; you've more than prepared me to make an impact here. When I started dabbling in automated testing I had no idea how important it would be.
