Recommendations for unit testing PL/SQL programs

I have recently received a couple of requests for recommendations regarding unit testing of PL/SQL programs. I thought I would share with you what I told them.

First, some background: unit testing refers to the process of testing individual subprograms for correctness, as opposed to overall application testing (which, these days, almost always means visiting a website).

The basic idea behind unit testing is that if you verify that each individual subprogram works correctly, then you are much less likely to have bugs in higher-level programs that call those tested subprograms. And when you do, you know you can focus on the way the tested subprograms are used, and not the subprograms themselves.

The most important application of a unit test is to participate in a regression test, which can be run to verify one's code works today as well as it did yesterday. That will greatly reduce the chance of you upgrading the application and users complaining that a bunch of features that worked in V1.2 no longer work in the fancy, new and improved V1.3.

That's always embarrassing, right?

When developers talk about unit testing, they are usually not just talking about writing and running a test. They are talking about finding and using a framework that automates as much of the process as possible. Automation is key because testing always takes a lot of time, and manual testing takes the most time of all.

And most dev managers are not going to give you lots of time for testing. That's just the way it is.

Here are the main steps that could be automated:

• Define the test cases: what are the various scenarios that need to be verified? These are driven by user requirements.
• Build the code that implements the test cases.
• Set up the data so that the test code will run properly.
• Run the tests.
• Record and examine the results.

There are a number of different tools available for automated or semi-automated unit testing of PL/SQL code. They include:

1. utPLSQL: "Junit for PL/SQL", an open source framework that I originally built in 1999 and is now managed by Paul Walker.
2. SQL Developer Unit Testing: Oracle's free PL/SQL IDE offers integrated unit testing.
3. Dell Code Tester for Oracle: a commercial unit testing tool that I originally designed and helped to build, sold as part of the Toad Development Suite for Oracle.

and then a bunch of others, including DBFit, PL/unit, PLUTO and ruby-plsql, to name a few. I am not going to pretend that I have any great familiarity with this list of "others." I encourage you to check them out; I am going to focus on the Big Three. [Note: PL/SQL Developer, another popular PL/SQL IDE has some very basic unit testing features, but not enough to warrant review in this post.]

And even there, I am not going to offer a detailed comparison of features. You will need to do that yourself, based on your requirements. Instead, I will take a step back and offer some high level guidance to get you started on your journey.

utPLSQL

First, it is important to correct a misunderstanding that some people have, and the creator of ruby-plsql repeats in his blog: "Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project." Raimonds is absolutely correct that I stopped working on utPLSQL years ago, and for a while it did languish. 

Last year, however, Paul Walker stepped up to the plate and took over managing the project. He has been applying bug fixes, improving documentation, and encouraging discussion and collaboration.

Since 2006, utPLSQL has been downloaded over 76,000 times. Of course, there's no way of knowing how many dev teams use it, but I think it is safe to say there are hundreds of groups around the world actively relying on utPLSQL.

utPLSQL is free and gives you total control over your test code. But that's mostly because you have to write all the test code (SQL Developer and Code Tester generate test code for you). And then you have to maintain all that test code. But once you've written your tests, built around the utPLSQL API, you can execute your tests with a single command, and utPLSQL will tell you whether or not your code passed (with unit testing, passing comes with 100% correctness).

utPLSQL test scripts can be integrated into continuous integration scripting.

Code Tester for Oracle

I think that it is safe to say, even given my perhaps doubly-subjective perspective of (a) being an original designer of Code Tester and (b) now working for "the competition" - Oracle, that Code Tester is the most sophisticated automated testing tool for PL/SQL. It's packed full of features, handles lots of complex scenarios (for example, if your parameter is a collection of records and the record contains a collection of objects, you can right inside the UI provide values for that parameter), generates test code from your specifications, and offers limited integration with Toad.

That sophistication comes, however, at a steep price, namely $2854 for the Toad Development Suite for Oracle. You can't buy Code Tester by itself.

So here's my recommendation: if you are licensed to use the Toad Development Suite for Oracle (even if you only use Toad, you may have a license to TDSO via a site license, and not even know it), you owe it to yourself and your team to check out Code Tester. 

You will likely run into some frustrations and limitations, but it does an awful lot of the heavy lifting for you.

Code Tester tests can be integrated into continuous integration scripting via a PL/SQL API.

SQL Developer Unit Testing

Last and definitely not least, is the unit testing feature integrated directly into SQL Developer. Let's stop right there to make sure you appreciate what I just said:

The automated unit testing feature of SQL Developer is not a separate tool from the editor (which is the case with Code Tester - it is external to Toad, with only minimal integration points). It is a part of SQL Developer. This is, I believe a crucial point, because developers do not like to leave their editors.  Instead, they want all necessary functionality brought to them inside their editors.

Here is one screenshot from the doc to give you a sense of the integration:

In many ways, SQL Developer Unit Testing and Code Tester for Oracle are similar. They both aim to take the burden off of you for writing test code. Instead, you describe the test case: What are the input values? What data needs to be set up? Most importantly, what are the expected outcomes?

SQL Developer then generates code that implements your test case. If you need to change the test, you don't have to modify test code (as you would do with utPLSQL), you go into the UI, make a change, and run your test again.

SQL Dev unit tests can also be integrated into continuous integration scripting.

So here's my recommendation: 

If you are already using SQL Developer, the decision should be clear. Try the integrated unit testing features. See if it meets your needs (at least enough to get you going, building some tests, experiencing the positive feedback loop). 

If you are not a SQL Developer user, let's say you use Toad or PL/SQL Developer, and you are not likely to get access to Code Tester for Oracle, then I also suggest you install SQL Developer and try its unit testing features. Sure, it would be better to just use one IDE, but if the automated testing of SQL Dev works for you, given its infinitely low cost (as in, free), I would think you could justify having that second IDE around solely for testing.

Overall Conclusions

If you have access to an IDE that offers automated unit testing, including the generation of test code from test case specifications, try that first. The productivity gains of test code generation are so massive, it can easily be worth accepting some limitations.

If you are allergic to UI-based testing and/or worry about being constrained by an IDE's feature set (in other words, you are a command line fanatic), utPLSQL offers a powerful alternative. It's proven to be useful to hundreds and maybe thousands of dev teams over the years. It is an actively maintained and enhanced open source project.

Your Experience?

Do you use an automated testing framework for PL/SQL? Or have you built your own? Please tell us about your own experience.