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.

On to Part 5