Unit Testing PL/SQL

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.

One thought on “Unit Testing PL/SQL

  1. utPLSQL is pretty neat, from what I remember. The hard part is keeping your test data clean and separate, which ends up involving individual, identical test schemas for every developer. DBAs love that.
    Also, if your programmers (assuming you’re on a team) aren’t used to making sure their procedures *don’t* cross transaction boundaries, writing your tear-down functions is a royal PITA.

Comments are closed.