Unit testing in Oracle PLSQL
Posted by Mike Haller
on Tuesday, March 25. 2008
at 18:48
in SQL
As a Java Developer, I love to develop test-driven. The 21st century is all about testing and delivering quality software. Today, my first Oracle PL/SQL Function Unit Test was born. It's not as ugly as I thought it would be, so i'm sharing my experience with you.Let's dig right into it. I wrote a small function for discounting amounts. This is the package header (e.g. the interface):
FUNCTION f_Discount(
p_ClosingDate IN date,
p_Amount IN number,
p_DueDate IN date)
RETURN number;
This is part of the package called
DISCOUNTING. I won't publish the body here - the implementation details are of no relevance for this article.How do I write a unit test in PLSQL?
After you or your DBA installed utPLSQL, create a new package called
UT_DISCOUNTING. According to the naming convention of utPLSQL, the test package needs to be UT_packageundertest. The prefix can be changed though.Then, declare the header and implement the body of your test package:
CREATE OR REPLACE package UT_DISCOUNTING IS
-- Author : HAMI
-- Created : 25.03.08 11:44:28
-- Purpose : Unit Tests for Discounting
-- Test lifecycle, not needed in this example
PROCEDURE ut_setup;
PROCEDURE ut_teardown;
-- The tests:
PROCEDURE ut_f_discount;
PROCEDURE ut_f_discount_samedate;
PROCEDURE ut_f_discount_zeroamount;
PROCEDURE ut_f_discount_closingdate;
PROCEDURE ut_f_discount_amount;
PROCEDURE ut_f_discount_duedate;
end UT_DISCOUNTING;
Now comes the interesting part, the test case implementations in the body of the test package:
CREATE OR REPLACE package body UT_DISCOUNTING IS
PROCEDURE ut_setup IS
BEGIN
NULL;
END;
PROCEDURE ut_teardown IS
BEGIN
NULL;
END;
PROCEDURE ut_f_discount IS
BEGIN
utAssert.eq('Typical valid usage',
discounting.f_discount(
p_closingdate => TO_DATE('25.03.08','dd.mm.yy'),
p_amount => 100.00,
p_duedate => TO_DATE('01.03.11', 'dd.mm.yy')),
89.3767); -- Expected result
END;
...
end UT_DISCOUNTING;
Finally, you want to run your test cases, right?
This is done by executing the utPLSQL function in a SQLPlus command window:
exec utplsql.test ('DISCOUNTING')
Well, you won't get a green bar (the Oracle guys do not seem to like colors), but you'll get an oversized success message, which will hopefully give you the same feelings:
And that's it folks! Thanks for reading
And in case you want to read more:
How to test for Exceptions
If you want to test for exceptions, you can either use the utAssert.throws() function,
or do it the long way like below. The advantage of the long way is that you can test
for the error message more easier. The advantage of using utAssert.throws() is better
readibility.
PROCEDURE ut_f_discount_duedate IS
tmp NUMERIC;
BEGIN
SELECT discounting.f_discount(
TO_DATE('25.03.08', 'dd.mm.yy'),
100,NULL) INTO tmp FROM DUAL;
utAssert.this('Exception expected', FALSE);
EXCEPTION
WHEN OTHERS THEN
utAssert.eq('Usererror 20004', SQLCODE, -20004);
utAssert.eq('Message should contain cause',
SQLERRM, 'ORA-20004: DueDate is required');
END;
As homework for the reader (and for me):
How to refactor the function so it doesn't use SQL statements directly?
(The original function uses an embedded SQL statement a la SELECT rate FROM rates_view)
Links
