TDD Tutorial 2

Interacting with the database using PL/Unit

Note: If you've not followed the first tutorial, you should do so before starting this one, as we'll be building upon the previous examples.

Most code written using PL/SQL accesses the database for some purpose, either to select some data, or to modify some data. Testing stored PL/SQL that relies on data to be in the database presents some unique challenges. The main goal of any unit testing suite is to be able to be executed over and over again, without regard to the state of the system it is running under. PL/Unit tests are no exception. Translating to the Oracle realm, what this means is that we need to have some way of ensuring a consistent state of the data that our unit tests use. Fortunately, this is easily done if given some thought.

PL/Unit and database sessions

One important piece of information to note is that there are two sessions used by Apollo Pro for each SQL window created. One session that is executing the PL/Unit tests, and another that is used to execute queries and DML against the database. This is important because inserts, updates or deletes performed in the SQL window will not be visible to PL/Unit tests unless committed. Conversely, DML performed in a PL/Unit test will not be visible to queries run in the SQL editor unless committed.

Setting up consistent test data and cleaning up afterwards

To see how we can ensure consistent test data every time our PL/Unit tests run, we'll continue with our example from the first tutorial. What we have so far is a function that, if passed a salary and commission, will calculate the percentage of commission to salary. If you recall, the customer asked for this percentage to be calculated for each employee in the company. Our employee data is stored in the EMP table, and what we need to do next is to make sure that the function we've written works correctly when applied in an SQL statement.

Following the Test Driven Development model, our first step is to write a failing test. We'll add this test to our original four tests. Our basic plan of attack for this test will be to insert a row into EMP, select that row using our function, asserting a correct result, and rolling back our insert. In this way, we will guarantee that our test has access to the same data each time it is run, and we will also be leaving the database unchanged after our test runs.

Setup and Teardown

In order to accomplish this new test, we'll be introducing two special PL/Unit procedures: Setup and Teardown. The Setup procedure is designed to run right before each and every test procedure in the test package, and teardown is designed to run after each test procedure is finished. Teardown will run regardless of the outcome of the test itself, so passed, failed, and erroneous tests will always call Teardown.

We'll use the Setup procedure to insert our data, and the Teardown procedure to rollback the insert. Enter and run the following test to see the results:

CREATE OR REPLACE PACKAGE test_calc_comm_percent AS

-- PL/Unit tests are implemented as packages

PROCEDURE setup;

PROCEDURE teardown;

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;

PROCEDURE t_zero_both;

PROCEDURE t_database_call;

END;

CREATE OR REPLACE PACKAGE BODY test_calc_comm_percent AS

PROCEDURE setup IS

BEGIN

INSERT INTO emp VALUES (1, 'SMITH', 'CODER', 7782, to_date('23-JAN-82'), 1000, 100, 10);

END;

PROCEDURE teardown IS

BEGIN

ROLLBACK;

END;

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;

PROCEDURE t_zero_both IS

BEGIN

-- with no commission and no salary, the percentage should be zero

plunit.assert_equals(0, calc_comm_percent(0, 0), 'Commission percent is wrong');

END;

PROCEDURE t_database_call IS

BEGIN

plunit.assert_query_equals_value('select calc_comm_percent(sal, comm) from emp where empno = 1', 0.2, 'Wrong commission percent returned');

END;

Running the test above should result in Red Bar. Examining the results shows that we expected 0.2 but actually got 0.1. But wait! 0.1 is the correct answer, so why did we assert for 0.2? The answer lies in the fact that, in TDD, we're supposed to write a failing test to begin with. So, we purposely asserted an incorrect value to make the test fail. To make the test pass, we change the 0.2 to 0.1, and rerun the test. This results in Green Bar. While this may seem extremely contrived and a waste of time, the purpose of starting with a failing test is quite simple. It validates that your new test was actually run. Had we started with a passing test by asserting for 0.1, but had mistyped the test name as tdatabase_call (not starting with t_), such that PL/Unit wouldn't actually run the test, how would we know? Scanning the list of tests for our new test may be one way, but when you've built up hundreds of tests, that is not feasible. So, start with a failing test, and expect to see Red Bar, then fix the problem.

At this point, you can verify that the row we inserted into the database was not committed by opening a third editor page and trying to select that row. You'll also notice that we used a new assertion routine in t_database_call: assert_query_equals_value. This assertion does what it's name implies, it makes sure that the result of the query equals the expected value given. This assertion can only be used when the query returns exactly one row and one column. For more information on PL/Unit assertions, PL/Unit Assertions.

The next step in this process would be to create test rows that duplicate all possible data values that can be passed into the calc_comm_percent row. That is an exercise best left to the reader.

Other points

We've explored one aspect of test data, and how PL/Unit can interact with it. There is another aspect yet to be explored, and that is the area of transactions within stored procedures. Unit tests are meant to be run over and over again, and one must take care to understand the ramifications of this. If your unit tests are calling a stored procedure that inserts data into the database and then commits, you'll need to realize that each time you run the unit tests, you're creating data and ending transactions that could potentially commit other test data (data inserted inside unit tests for example). Some ways to resolve this include performing deletes in the Teardown procedure, passing parameters into stored procedures to indicate whether to commit, or to remove the commit from the stored procedure altogether. We recommend the last option, as it leaves the most flexibility in both the PL/Unit tests, as well as in other referring PL/SQL code.

Finally, there is one important point to make. In no case should you ever trap exceptions in a unit test procedure using when others without re-raising the exception. Doing so will result in false positives, as PL/Unit uses exceptions to indicate a failed test.

Summary

What we learned in this example was how to use PL/Unit features to interact with the database, using Setup and Teardown, the importance of starting with a failing test, and handling transactions. While transaction handling can sometimes be tricky to resolve in the context of unit testing, take our word for it, the benefits are worth it in the long run.

Happy unit testing!