For my sins I’ve been writing a bit of PL/SQL recently. It’s been nearly 4 years since I had to do that in anger and predictably I’ve forgotten way more than I remember. At the time I was responsible for redesigning the database for a Laboratory Information Management System used by researchers at Pfizer looking for new drugs. After redesigning the data model I had to write code to port from one to the other. That was a lot of code, and required a lot of testing. Fun project though, and an interesting application.
Of course now I know all about test driven development and the first thing that occured to me was: “how do I test this stuff?”.
As most of what I’ve been doing at the moment is just simple functions (data clean-up stuff mostly) I’ve been able to get away with a simple 3 column table containing original, generated, and expected data columns. I can then do a single UPDATE statement to process all rows, and a single SELECT to query any rows where generated != expected. Good enough for most simple testing exercises I think.
However via InternetAlchemy I came across the utPLSQL project today. Which looks suitable for more detailed testing of PL/SQL procedures.
And as I fear there may be more PL/SQL development, along with testing of a large scale data migration in my future, I think it’s going to be pretty useful.