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.

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.

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

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.

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.