Oracle Unit Testing

Part 4: How do Unit Tests work?

Unit Testing 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/ 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

ut_p_te_customer.pks

CREATE OR REPLACE package p_te_customer			is			/*			<version>1.1</version>			<filename>p_te_customer.spc</filename>			<summary>Package encapsulation for the CUSTOMER table.</summary>			<overview>			This package contains services for managing table data.			</overview>			<scope>Oracle Unit Testing</scope>			*/			-- 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;			/

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.

p_te_customer.pkb

ut_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;			/

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;			/

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.oracledeveloper.nl/

A similar approach is used for the Object tier. Firstly look at the package specifications.

p_obj_customer.pks

ut_p_obj_customer.pks

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

ut_p_obj_customer.pkb

CREATE OR REPLACE package body p_obj_customer			as			/******************************************************************************			NAME:       p_obj_customer			PURPOSE:			REVISIONS:			Ver        Date        Author           Description			---------  ----------  ---------------  ------------------------------------			1.0        04/10/2006                      1. Created this package body.			******************************************************************************/			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;			/

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. Remember, if this is all getting too complex too quickly, go back up to the diagram and study it again.

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.

On to Part 5 >>

Oracle Unit Testing

Part 4: How do Unit Tests work?

Search

Popular links

Part 4: How do Unit Tests work?