Unit Testing Oracle PL/SQL

Submitted by Thomas on Wed, 11/17/2010 - 00:00

Unit testing stored procedures is usually a pain. Database development don't seem to have the xUnit culture we have on the application tier. But there are some good tools if you look.

If you use Oracle, PLUnit is a necessity. The other Oracle test frameworks, PLUTO and utPLSQL, seem to want to force an OOP approach on plsql.

CREATE OR REPLACE PACKAGE test_calc_comm_percent AS
     -- PL/Unit tests are implemented as packages
 
     PROCEDURE t_call_function;
     -- unit tests are public procedures that have no parameters
     -- their names must begin with t_
 
     PROCEDURE t_zero_salary;
     PROCEDURE t_zero_comm;
 
END;
/
 
CREATE OR REPLACE PACKAGE BODY test_calc_comm_percent AS
     PROCEDURE t_call_function IS
          -- we write our test logic in the package body
     BEGIN
          -- our call to assert equals is taking 3 parameters.  
          -- The first is the value we expect, the second is the actual
          -- value (our function result)
          -- the third is the optional error message to supply if the
          -- test fails.
          plunit.assert_equals(0.10, calc_comm_percent(1000, 100),
               'Commission percent is wrong');
     END;
 
     PROCEDURE t_zero_salary IS
     BEGIN
          -- we'll assume that with no salary, the commission makes
          -- up 100% of the salary
          plunit.assert_equals(1, calc_comm_percent(0, 100),
               'Commission percent is wrong');
     END;
 
     PROCEDURE t_zero_comm IS
          BEGIN
               -- with no commission, the percentage should be zero
               plunit.assert_equals(0, calc_comm_percent(1000, 0),
                    'Commission percent is wrong');
          END;
END;
/

Beautiful, I can now code mission critical PL/SQL without fear !