Sep 212012

Of course you unit test your code! Everybody does that. Your company, your co-developers and your conscience tells you it’s the right thing to do. We’ve been practicing this for years: at work, in conferences, during code dojos, and even at home hacking sessions. But do you unit test your PL/SQL packages? Well I don’t believe you do. Or at least I didn’t. But in my last assignment I actually found a way to do it. This post suggests a way for creating unit tests inside PL/SQL packages.

The Problem

In my last assignment we moved lots of business logic from Java into PL/SQL packages. Whether or not this is a good idea is beyond the scope of this post. But now that the logic is in place, we needed a way to test it. People involved in the project suggested that we simply processed the same data using the Java logic and then the PL/SQL logic and then compare the results. This is a good idea, I answered, but it’s not enough! I’d like to be able to test individual functions and procedures inside the packages and make sure they do what they should, and I want to do it automatically, and be informed instantly when things are broken. In short, I’d like to unit test the packages. Ok, they answered, that sounds promising enough, but how are you going to do that? Packages expose only few procedures and functions in their headers, and these are often implemented by calling multiple other procedures and functions encapsulated inside the package which are kept hidden from the outside. Testing these exposed procedure can’t possibly be considered unit testing?

The Solution

The idea is to do the testing inside the package. That way, you can test the logic implemented in all the procedures and functions that are hidden inside the package. We’re exposing one more procedure in the header of each package, we call it runTests. Its implementation consists on simply calling all the other test procedures that are implemented inside the package. Also, we’ve created a new package called asserts, wherein we implement procedures such as assert and assertEquals. The body of the assert package looks like this:

create or replace
package body asserts as

	-- raise an error given a procedure and a message
	procedure raiseError(proc in varchar2, message in varchar2) is
		raise_application_error(-20000, 'Error in ' || proc || ': ' || message);
	end raiseerror;

	-- assert a condition
	procedure assert(
		condition in boolean,
		proc in varchar2,
		message in varchar2) is
		if not condition then
			raiseError(proc, message);
		end if;
	end assert;

	-- assert that given values are equal
	procedure assertEquals(
		expected in varchar2,
		actual in varchar2,
		proc in varchar2) is
		if not expected = actual then
			raiseError(proc, 'expected ' || expected || ', got ' || actual);
		end if;
	end assertEquals;

end asserts;

The implementation is not totally unlike JUnit and other unit test frameworks. We decided that every call to assert or assertEquals must include the name of the test procedure (the proc argument), simply because we couldn’t find a decent way to retrieve it otherwise. This will insure that the error raised when the test fails will include the name of the failing test. Also, this package implements only two methods. Need more? Write them yourself! These methods were sufficient for our needs.

Below is the body of an example package including a “business logic” function (addStars) and its unit tests.

create or replace
package body myPackage as

	-- a function that adds stars before and after a string
	function addStars(s varchar2) return varchar2 is
		return '*** ' || s || ' ***';
	end addStars;

	-- tests that addStart does not return null
	procedure addStarsTest1 as
		asserts.assert(addStars('test') is not null, 'addStartsTest1', 'addStarts should not return null');
	end addStarsTest1;

	-- tests that addStarts return correct result
	procedure addStarsTest2 as
		asserts.assertEquals('*** test ***', addStars('test'), 'addStartsTest2');
	end addStarsTest2;

	-- failing test
	procedure addStarsTest3 as
		asserts.assertEquals('### test ###', addStars('test'), 'addStarsTest3');
	end addStarsTest3;

	-- run all tests
	procedure runTests as
	end runTests;

end myPackage;

As you can see, the third test should fail. When we run the runTests procedure, we get the following error message, which includes the failing test procedure name, and the difference between the expected and given values:

Error starting at line 1 in command:
execute mypackage.runTests
Error report:
ORA-20000: Error in addStarsTest3: expected ### test ###, got *** test ***
ORA-06512: at "WHATEVER.ASSERTS", line 5
ORA-06512: at "WHATEVER.ASSERTS", line 24
ORA-06512: at "WHATEVER.MYPACKAGE", line 24
ORA-06512: at "WHATEVER.MYPACKAGE", line 32
ORA-06512: at line 1

So there you have it. You can call the runTests procedure from any capable build tool and thus run automatic unit tests continuously and be informed whenever things get broken. We’re using DbUnit and Maven, so that our db unit tests run whenever other unit tests do. Doing that enabled us to add db unit tests to our continuous integration process.

Some Q&A

How about mocking?
Well I don’t know if mocking is possible, but what you can do is to use for example DbUnit to fill your tables with mocked data before running your tests.

Is the asserts package available anywhere?
Glad that you asked! It’s available in Github where you can download it, clone it and contribute to it.

Why does the asserts package only contain assert or assertEquals procedures?
Because that was sufficient for our needs. Feel free to add whatever procedures you like to the package.

How about utPLSQL? It looks much better!
I couldn’t agree more. It look great. But as I understand it it doesn’t give you the opportunity to unit test inside your own packages, since it requires that you create test packages (which is a great way to test your API, but not the inside of it.)

  One Response to “How to Unit Test Your PL/SQL Packages”

  1. Somehow i understand the concept..i need few more examples..

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>