Oracle and PL/SQL Recipes: A Problem-Solution Approach

17-4. Writing a utPLSQL Unit Test Procedure

Problem

You have a PL/SQL object that you'd like to test to verify it returns the expected values.

Solution

Create a utPLSQL test package to test every code branch and computation within your function. Use utPLSQL assertion statements to test every foreseeable use case for the function. For example, suppose you wish to test a simple factorial function that contains four code branches, each of which returns a value. Here's the target function:

CREATE OR REPLACE FUNCTION factorial (fact INTEGER) RETURN INTEGER is BEGIN    IF fact < 0 THEN RETURN NULL;    ELSIF fact = 0 THEN RETURN 1;    ELSIF fact = 1 THEN RETURN fact;    ELSE RETURN fact * factorial (fact-1);    END IF; END factorial;

Next, create ...