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.
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 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
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 begin 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 begin 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 begin 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
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 begin return '*** ' || s || ' ***'; end addStars; -- tests that addStart does not return null procedure addStarsTest1 as begin asserts.assert(addStars('test') is not null, 'addStartsTest1', 'addStarts should not return null'); end addStarsTest1; -- tests that addStarts return correct result procedure addStarsTest2 as begin asserts.assertEquals('*** test ***', addStars('test'), 'addStartsTest2'); end addStarsTest2; -- failing test procedure addStarsTest3 as begin asserts.assertEquals('### test ###', addStars('test'), 'addStarsTest3'); end addStarsTest3; -- run all tests procedure runTests as begin addStarsTest1; addStarsTest2; addStarsTest3; 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.
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
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.)