Part 4: How do Unit Tests work?
There are many unit test frameworks available for most common languages in use today.
In terms of Oracle Unit Testing there really is only one choice, UTPLSQL.
UTPLSQL can be freely downloaded from the main site at http://utplsql.sourceforge.net/ (or by clicking the download link on the right hand side menu –>>) and basically consists of a package structure which is installed on the Oracle database server usually into its own schema. Full install instructions can be found at the aforementioned site.
UTPLSQL has a number of procedures that can be called to verify results from your package code. And this is basically all we are doing, verifying results when you run your code.
Let’s take a look at the specification of both the main unit and the test for the p_te_customer package.
p_te_customer.pks
create or replace package p_te_customer
is
-- Record declaration for single/composite primary key
type rt_pky is record (customer_id customer.customer_id%type);
-- Record declaration for customer
type rt_allcols is record
(
customer_id customer.customer_id%type,
account_number customer.account_number%type,
date_created customer.date_created%type,
date_closed customer.date_closed%type,
last_billed_date customer.last_billed_date%type,
customer_name customer.customer_name%type,
address_1 customer.address_1%type,
address_2 customer.address_2%type,
city customer.city%type,
post_code customer.post_code%type,
country customer.country%type,
customer_type customer.customer_type%type
); -- Cursor declaration for job_schedule_history records
type rc_customer is ref cursor
return rt_allcols;
-- Column collection types
type it_customer_id is table of customer.customer_id%type
index by pls_integer;
type it_account_number is table of customer.account_number%type
index by pls_integer;
type it_date_created is table of customer.date_created%type
index by pls_integer;
type it_date_closed is table of customer.date_closed%type
index by pls_integer;
type it_last_billed_date is table of customer.last_billed_date%type
index by pls_integer;
type it_customer_name is table of customer.customer_name%type
index by pls_integer;
type it_address_1 is table of customer.address_1%type
index by pls_integer;
type it_address_2 is table of customer.address_2%type
index by pls_integer;
type it_city is table of customer.city%type
index by pls_integer;
type it_post_code is table of customer.post_code%type
index by pls_integer;
type it_country is table of customer.country%type
index by pls_integer;
type it_customer_type is table of customer.customer_type%type
index by pls_integer;
procedure add (p_customer_id in out customer.customer_id%type,
p_account_number in customer.account_number%type,
p_date_created in customer.date_created%type,
p_date_closed in customer.date_closed%type,
p_last_billed_date in customer.last_billed_date%type,
p_customer_name in customer.customer_name%type,
p_address_1 in customer.address_1%type,
p_address_2 in customer.address_2%type,
p_city in customer.city%type,
p_post_code in customer.post_code%type,
p_country in customer.country%type,
p_customer_type in customer.customer_type%type);
procedure get (p_customer_id in customer.customer_id%type, prt_customer out rt_allcols);
procedure del (p_customer_id in customer.customer_id%type, p_deleted out pls_integer);
procedure upd (p_customer_id in out customer.customer_id%type,
p_account_number in customer.account_number%type,
p_date_created in customer.date_created%type,
p_date_closed in customer.date_closed%type,
p_last_billed_date in customer.last_billed_date%type,
p_customer_name in customer.customer_name%type,
p_address_1 in customer.address_1%type,
p_address_2 in customer.address_2%type,
p_city in customer.city%type,
p_post_code in customer.post_code%type,
p_country in customer.country%type,
p_customer_type in customer.customer_type%type);
end p_te_customer;
/
ut_p_te_customer.pks
create or replace package ut_p_te_customer
is
/** Rolls back the data required to perform the tests. */
procedure ut_setup;
procedure ut_teardown;
procedure ut_add;
procedure ut_get;
procedure ut_del;
procedure ut_upd;
end ut_p_te_customer;
/
So we can see just by looking at the specifications of the two packages above that the unit test package (ut_p_te_customer) is mirroring the main package(p_te_customer) by individually calling all if its procedures.
Also note the two additional procedures ut_setup and ut_teardown. These procedures are used firstly to set up data that you will use in your unit test, and then eventually teardown (or rollback) the data used. For the vast majority of times you would want to be working in a “pre-commit” basis. The theory being that you should be able to interact and test the database without interferring with its continuity.
Let’s now take a closer look at the body of these two packages. Looking at the test package in detail you can see that it calls each of the procedures in the main package. But wait, there’s more! It’s not enough to just call the procedure, you have to monitor the result. We do this by calling one of the many UTPLSQL procedures available to us. There are too many to describe in the scope of this website, but here’s a snippet (from line 34) of what we might use. utassert.eq(‘Check for customer number after ADD procedure’, vrt_customer.account_number, 12345); This is probably the most common procedure you would use in UTPLSQL, and basically you are looking at the value contained in vrt_customer (variable of record type), attribute account_number. You may also have a need for a “null” check on a result. Line 71 shows an example of this. utassert.isnull(‘Check that row has been deleted’, vrt_customer.account_number); As I said, there are many functions within UTPLSQL that can test all manner of Oracle objects/procedures/funtions, but since this is a simple guide to oracle unit testing, I’ll leave it at that. Although, please do check out the documentation for UTPLSQL at http://utplsql.sourceforge.net/
A similar approach is used for the Object tier. Firstly look at the package specifications.
p_te_customer.pkb
create or replace package body p_te_customer
is
procedure add (p_customer_id in out customer.customer_id%type,
p_account_number in customer.account_number%type,
p_date_created in customer.date_created%type,
p_date_closed in customer.date_closed%type,
p_last_billed_date in customer.last_billed_date%type,
p_customer_name in customer.customer_name%type,
p_address_1 in customer.address_1%type,
p_address_2 in customer.address_2%type,
p_city in customer.city%type,
p_post_code in customer.post_code%type,
p_country in customer.country%type,
p_customer_type in customer.customer_type%type)
is
begin
insert into customer (customer_id,
account_number,
date_created,
date_closed,
last_billed_date,
customer_name,
address_1,
address_2,
city,
post_code,
country,
customer_type)
values (p_customer_id,
p_account_number,
p_date_created,
p_date_closed,
p_last_billed_date,
p_customer_name,
p_address_1,
p_address_2,
p_city,
p_post_code,
p_country,
p_customer_type);
end add;
procedure get (p_customer_id in customer.customer_id%type, prt_customer out rt_allcols)
is
prc_customer p_te_customer.rc_customer;
begin
open prc_customer for
select customer_id,
account_number,
date_created,
date_closed,
last_billed_date,
customer_name,
address_1,
address_2,
city,
post_code,
country,
customer_type
from customer
where customer_id = p_customer_id;
fetch prc_customer into prt_customer;
close prc_customer;
end get;
procedure del (p_customer_id in customer.customer_id%type, p_deleted out pls_integer)
is
begin
delete from customer
where customer_id = p_customer_id;
p_deleted := 1;
end del;
procedure upd (p_customer_id in out customer.customer_id%type,
p_account_number in customer.account_number%type,
p_date_created in customer.date_created%type,
p_date_closed in customer.date_closed%type,
p_last_billed_date in customer.last_billed_date%type,
p_customer_name in customer.customer_name%type,
p_address_1 in customer.address_1%type,
p_address_2 in customer.address_2%type,
p_city in customer.city%type,
p_post_code in customer.post_code%type,
p_country in customer.country%type,
p_customer_type in customer.customer_type%type)
is
begin
update customer
set customer_id = p_customer_id,
account_number = p_account_number,
date_created = p_date_created,
date_closed = p_date_closed,
last_billed_date = p_last_billed_date,
customer_name = p_customer_name,
address_1 = p_address_1,
address_2 = p_address_2,
city = p_city,
post_code = p_post_code,
country = p_country,
customer_type = p_customer_type
where customer_id = p_customer_id;
end upd;
end p_te_customer;
/
ut_p_te_customer.pkb
create or replace package body ut_p_te_customer
is
v_customer_id customer.customer_id%type := 1;
procedure ut_setup
is
begin
--set up test data
p_te_customer.add (p_customer_id => v_customer_id,
p_account_number => 12345,
p_date_created => sysdate,
p_date_closed => sysdate,
p_last_billed_date => sysdate,
p_customer_name => 'John Smith',
p_address_1 => '123 First Street',
p_address_2 => 'Eastside',
p_city => 'NewCity',
p_post_code => 'ST11 4RT',
p_country => 'Britain',
p_customer_type => 'Preferred');
end;
procedure ut_teardown
is
begin
rollback;
end;
procedure ut_add
is
vrt_customer p_te_customer.rt_allcols;
begin
p_te_customer.get (p_customer_id => v_customer_id, prt_customer => vrt_customer);
utassert.
eq ('Check for customer number after ADD procedure', vrt_customer.account_number, 12345);
end;
procedure ut_get
is
vrt_customer p_te_customer.rt_allcols;
begin
v_customer_id := 1;
p_te_customer.get (p_customer_id => v_customer_id, prt_customer => vrt_customer);
utassert.eq ('Check for customer number', vrt_customer.account_number, 12345);
end;
procedure ut_del
is
v_deleted pls_integer;
vrt_customer p_te_customer.rt_allcols;
begin
v_customer_id := 3;
p_te_customer.add (p_customer_id => v_customer_id,
p_account_number => 12345,
p_date_created => sysdate,
p_date_closed => sysdate,
p_last_billed_date => sysdate,
p_customer_name => 'John Smith',
p_address_1 => '123 First Street',
p_address_2 => 'Eastside',
p_city => 'NewCity',
p_post_code => 'ST11 4RT',
p_country => 'Britain',
p_customer_type => 'Preferred');
p_te_customer.del (p_customer_id => v_customer_id, p_deleted => v_deleted);
p_te_customer.get (p_customer_id => v_customer_id, prt_customer => vrt_customer);
utassert.isnull ('Check that row has been deleted', vrt_customer.account_number);
utassert.eq ('Check for p_deleted flag set to 1', v_deleted, 1);
end;
procedure ut_upd
is
vrt_customer p_te_customer.rt_allcols;
begin
v_customer_id := 4;
p_te_customer.add (p_customer_id => v_customer_id,
p_account_number => 12345,
p_date_created => sysdate,
p_date_closed => sysdate,
p_last_billed_date => sysdate,
p_customer_name => ' john smith',
p_address_1 => '123 first street',
p_address_2 => 'eastside',
p_city => 'newcity',
p_post_code => 'st11 4rt',
p_country => 'britain',
p_customer_type => 'preferred');
p_te_customer.upd (p_customer_id => v_customer_id,
p_account_number => 54321,
p_date_created => sysdate,
p_date_closed => sysdate,
p_last_billed_date => sysdate,
p_customer_name => 'john jones',
p_address_1 => '123 first street',
p_address_2 => 'eastside',
p_city => 'newcity',
p_post_code => 'st11 4rt',
p_country => 'britain',
p_customer_type => 'preferred');
p_te_customer.get (p_customer_id => v_customer_id, prt_customer => vrt_customer);
utassert.eq ('check for updated account number', vrt_customer.account_number, 54321);
utassert.eq ('check for updated customer name', vrt_customer.customer_name, 'john jones');
end;
end ut_p_te_customer;
/
p_obj_customer.pks
create or replace package p_obj_customer
as
procedure add (pot_customer_vo in out ot_customer_vo);
procedure get (p_customer_id in customer.customer_id%type, pot_customer_vo out ot_customer_vo);
procedure upd (pot_customer_vo in out ot_customer_vo);
procedure del (p_customer_id in customer.customer_id%type);
end p_obj_customer;
/
ut_p_obj_customer.pks
create or replace package ut_p_obj_customer
is /** Sets up the objects required to perform the tests. */
procedure ut_setup; /** Drops the objects required to perform the tests. */
procedure ut_teardown;
procedure ut_add;
procedure ut_get;
procedure ut_upd;
procedure ut_del;
end ut_p_obj_customer;
/
Looks familiar? Well, in truth, its not all that different to what we’re doing in the Table Encapsulation. The main difference is that we are now dealing with objects rather than rows.
This is where we begin to dip into the new Object Oriented aspects of Oracle and make use of the OT_CUSTOMER_VO type. It’s worth noting that the naming convention I am using means “Object Type”+CUSTOMER+”Value Object”.
What’s a Value Object? It’s simply a “carrier” for the data that we are retrieving. This object can then be picked up by Java using JDBC. (A full explaination of Oracle Object Oriented design is outwith the scope of this site but further reading can be found here http://www.dba-oracle.com/art_oracle_obj.htm)
So let’s complete this example by looking at the body of the object packages.
p_obj_customer.pkb
create or replace package body p_obj_customer
as
procedure add (pot_customer_vo in out ot_customer_vo)
is
begin
p_te_customer.add (p_customer_id => pot_customer_vo.customer_id,
p_account_number => pot_customer_vo.account_number,
p_date_created => pot_customer_vo.date_created,
p_date_closed => pot_customer_vo.date_closed,
p_last_billed_date => pot_customer_vo.last_billed_date,
p_customer_name => pot_customer_vo.customer_name,
p_address_1 => pot_customer_vo.address_1,
p_address_2 => pot_customer_vo.address_2,
p_city => pot_customer_vo.city,
p_post_code => pot_customer_vo.post_code,
p_country => pot_customer_vo.country,
p_customer_type => pot_customer_vo.customer_type);
end;
procedure get (p_customer_id in customer.customer_id%type, pot_customer_vo out ot_customer_vo)
is
vrc_customer p_te_customer.rc_customer;
vrt_customer p_te_customer.rt_allcols;
vot_customer_vo ot_customer_vo;
vnt_customer_vo nt_customer_vo := nt_customer_vo ();
begin
p_te_customer.get (p_customer_id => p_customer_id, prt_customer => vrt_customer);
--initialise and populate ot_customer_vo
vot_customer_vo := null;
vot_customer_vo :=
new ot_customer_vo (customer_id => vrt_customer.customer_id,
account_number => vrt_customer.account_number,
date_created => vrt_customer.date_created,
date_closed => vrt_customer.date_closed,
last_billed_date => vrt_customer.last_billed_date,
customer_name => vrt_customer.customer_name,
address_1 => vrt_customer.address_1,
address_2 => vrt_customer.address_2,
city => vrt_customer.city,
post_code => vrt_customer.post_code,
country => vrt_customer.country,
customer_type => vrt_customer.customer_type);
pot_customer_vo := vot_customer_vo;
end;
procedure upd (pot_customer_vo in out ot_customer_vo)
is
begin
p_te_customer.upd (p_customer_id => pot_customer_vo.customer_id,
p_account_number => pot_customer_vo.account_number,
p_date_created => pot_customer_vo.date_created,
p_date_closed => pot_customer_vo.date_closed,
p_last_billed_date => pot_customer_vo.last_billed_date,
p_customer_name => pot_customer_vo.customer_name,
p_address_1 => pot_customer_vo.address_1,
p_address_2 => pot_customer_vo.address_2,
p_city => pot_customer_vo.city,
p_post_code => pot_customer_vo.post_code,
p_country => pot_customer_vo.country,
p_customer_type => pot_customer_vo.customer_type);
end;
procedure del (p_customer_id in customer.customer_id%type)
is
v_deleted pls_integer;
begin
p_te_customer.del (p_customer_id => p_customer_id, p_deleted => v_deleted);
end;
end p_obj_customer;
/
ut_p_obj_customer.pkb
create or replace package body ut_p_obj_customer
is
v_customer_id customer.customer_id%type := 1; /** Sets up the objects required to perform the tests. */
procedure ut_setup
is
begin
--set up test data
p_te_customer.add (p_customer_id => v_customer_id,
p_account_number => 12345,
p_date_created => sysdate,
p_date_closed => sysdate,
p_last_billed_date => sysdate,
p_customer_name => 'john smith',
p_address_1 => '123 first street',
p_address_2 => 'eastside',
p_city => 'newcity',
p_post_code => 'st11 4rt',
p_country => 'britain',
p_customer_type => 'preferred');
end; /** Drops the objects required to perform the tests. */
procedure ut_teardown
is
begin
rollback;
end;
procedure ut_add
is
vot_customer_vo ot_customer_vo;
vrt_customer p_te_customer.rt_allcols;
begin
v_customer_id := 2;
--create and populate vo
vot_customer_vo :=
new ot_customer_vo (customer_id => v_customer_id,
account_number => 12345,
date_created => sysdate,
date_closed => sysdate,
last_billed_date => sysdate,
customer_name => 'john smith',
address_1 => '123 first street',
address_2 => 'eastside',
city => 'newcity',
post_code => 'st11 4rt',
country => 'britain',
customer_type => 'preferred');
--add data
p_obj_customer.add (pot_customer_vo => vot_customer_vo);
--get back data
p_te_customer.get (p_customer_id => vot_customer_vo.customer_id, prt_customer => vrt_customer);
utassert.objexists ('check package exists', 'p_obj_customer', true);
utassert.eq ('check for correct customer number', vrt_customer.account_number, 12345);
end;
procedure ut_get
is
vot_customer_vo ot_customer_vo;
begin
v_customer_id := 1;
ut_setup ();
p_obj_customer.get (p_customer_id => v_customer_id, pot_customer_vo => vot_customer_vo);
utassert.
eq ('check for customer number as set in setup procedure',
vot_customer_vo.account_number,
12345);
end;
procedure ut_upd
is
vot_customer_vo ot_customer_vo;
begin
v_customer_id := 2;
vot_customer_vo :=
new ot_customer_vo (customer_id => v_customer_id,
account_number => 12345,
date_created => sysdate,
date_closed => sysdate,
last_billed_date => sysdate,
customer_name => 'john smith',
address_1 => '123 first street',
address_2 => 'eastside',
city => 'newcity',
post_code => 'st11 4rt',
country => 'britain',
customer_type => 'preferred');
p_obj_customer.add (pot_customer_vo => vot_customer_vo);
--check data
utassert.eq ('check data just entered', vot_customer_vo.account_number, 12345);
vot_customer_vo :=
new ot_customer_vo (customer_id => v_customer_id,
account_number => 54321,
date_created => sysdate,
date_closed => sysdate,
last_billed_date => sysdate,
customer_name => 'john smith',
address_1 => '123 first street',
address_2 => 'eastside',
city => 'newcity',
post_code => 'st11 4rt',
country => 'britain',
customer_type => 'preferred');
--now update
p_obj_customer.upd (pot_customer_vo => vot_customer_vo);
utassert.eq ('check data just entered', vot_customer_vo.account_number, 54321);
end;
procedure ut_del
is
vrt_customer p_te_customer.rt_allcols;
begin
p_obj_customer.del (p_customer_id => 1);
p_te_customer.get (p_customer_id => 1, prt_customer => vrt_customer);
utassert.isnull ('check that row has been deleted.', vrt_customer.account_number);
end;
end ut_p_obj_customer;
/
This is where we start making use of the “Value Objects”, so please take time to understand how to access these.
If this all seems complicated now, dont worry. It took me months to get used to this architecture. But once you achieve that level of complete understanding you can race through this type of code like it’s second nature.
And once you have mastered this style of Oracle coding, you are well on your way to building a truely multi-tiered, scalable and maintainable software application.
[…] Part 4: How do Unit Tests work? […]