< Previous Section: Test a Function | Next Section: Put Test Code in Same Package >

Test an Entire Package API

Most packages consist of lots more than a single program, and you will generally want to test each and every of the programs listed in the package specification. When you generate a test package with utGen, it will produce a template unit test procedure for each program in the package specification. You will then need to modify each of these programs.

One example of this more complex package structure is the table encapsulation package. This kind of package establishes a layer of code and therefore control between application requirements and underlying data structures. While the building of such a layer is uncommon in the world of PL/SQL developers, it is strongly recommended practice. A variety of tools, in fact, offer automated table encapsulation package generation, including Oracle Designer, RevealNet's PL/Generator and a variety of IDE (integrated development environment) tools.

Suppose, then, that I used PL/Generator to generate a table encapsulation package for the employee table. It would look like the code found in te_employee.pks and te_employee.pkb(1) (being rather lengthy, we will not reproduce it in the documentation. If you take a look, you will see that their are dozens of programs in the API, which means that you would have lots of work to do in building your unit test cases. In addition, many of the programs will be performing DML operations (updating, deleting, inserting). How you can easily and dependably test those programs?

When you are dealing with lots of programs that have a uniform structure and behavior (which should be the case if you are building table API packages), then you should look for ways to generate, rather than write manually, your test package. utGen cannot do this generation work for you, since the logic in your encapsulation package is specific to your environment.

You can, instead, build your own custom generator or use an existing generator that is sufficiently flexible to meet your needs. The original creator of utPLSQL, Steven Feuerstein, has also been working on generator utilities for a number of years. One of these utilities, currently "code named" GenX, came in very handy for creating a test package for his PL/Generator-generated encapsulation packages.

Using CGML (Code Generation Markup Language), Steven created a template (See te_utpkg.gdr in the Examples directory of the utPLSQL distribution) that reads information from the data dictionary and defines the setup, teardown and at least a good starting point for the unit test procedures. Here is the template logic for the setup procedure:

   PROCEDURE {utprefix}setup
   IS
   BEGIN
      -- Clean start
      {utprefix}teardown;
[ASIS]   
      -- Generic copy of base table for testing 
      EXECUTE IMMEDIATE 
         'CREATE TABLE {tabprefix}[objname] AS
            SELECT * FROM [objname]';
            
[ENDASIS]   
   [FOREACH]prog
   [IF]{allprogs}[EQ]Y[OR][progname][LIKE]UPD%[OR][progname][LIKE]INS%[OR][progname][LIKE]DEL%
      -- Create copy of base table for this unit test.
      EXECUTE IMMEDIATE 
         'CREATE TABLE ^{progtab}^ AS
[ASIS]   
            SELECT * FROM [objname]';
[ENDASIS]   
            
   [ENDIF]
   [ENDFOREACH]
   END;

You are not, of course, expected to understand all the logic and syntax in this fragment. If you are interested in pursuing these sorts of genreation opportunities and would like to check out GenX, drop a note to Steven Feuerstein.

Here is a portion of the generated logic (found in ut_te_employee.pks and ut_te_employee.pkb"(1)), the program that tests the delete operation in the encapsulation package:

   PROCEDURE ut_del1
   IS
      fdbk PLS_INTEGER;
   BEGIN
      /* Delete that finds now rows. */

      EXECUTE IMMEDIATE '
      DELETE FROM ut_DEL1
       WHERE employee_id = -1
      ';
      te_employee.del (-1, rowcount_out => fdbk);
      -- Test results
      utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
      /* Successful delete */

      EXECUTE IMMEDIATE '
      DELETE FROM ut_DEL1
       WHERE employee_id between 7800 and 7899
      ';

      FOR rec IN (SELECT *
                    FROM employee
                   WHERE employee_id BETWEEN 7800 AND 7899)
      LOOP
         te_employee.del (
            rec.employee_id,
            rowcount_out => fdbk
         );
      END LOOP;

      -- Test results
      utassert.eqtable ('Delete rows', 'EMPLOYEE', 'ut_DEL1');
      ROLLBACK;
   EXCEPTION
      WHEN OTHERS
      THEN
         utassert.this (
            'DEL1 exception ' || SQLERRM,
            SQLCODE = 0
         );
   END;

In this procedure, I test for two scenarios: a delete that removes zero rows and a delete that removes a specific set of rows. In both cases, I perform the explicit (non-encapsulated) DML logic against a copy of the actual table (this copy is created in the setup procedure; that is the reason I use dynamic SQL to refer to this table -- it doesn't exist when the package is compiled!). Then I do the (hopefully) same operation by using the API program. Finally, I call the appropriate utAssert assertion program to compare the results -- and at the end of the procedure issue a ROLLBACK so that my "source" table (employee, in this case), i set back to the original data state. Notice that I also put an assertion program in the exception section to trap any errors and flag it as a failed test.

That should give you a good feel for the kind of code you might write to test a table encapsulation package. The next two sections show you how I used the setup and teardown procedures to manage the data structures I use in my tests.

Set Up Data Structures

As I contemplated how best to test these large packages, I revisited some of my testing principles and found one to be of particular importance:

Build isolated tests.

This principle is important because it allows you to run one, all or a subset of your tests without having to worry about the impact or dependencies on the other tests. And test isolation is particularly important when testing DML operations. The way to validate a successful DML operation is by analyzing the contents of the "source" table against a "test" table. If all the tests modify the same test table, ti will be very difficult if not impossible to verify success or notice failure.

So I decided that the best way to run my unit tests for DML operations was to create a separate test table for each unit test. As a consequence, my setup procedure for the te_employee package looks like this: (See ut_te_employee.pkb in the Examples directory of the utPLSQL distribution)

   PROCEDURE ut_setup
   IS
   BEGIN
      ut_teardown;
      EXECUTE IMMEDIATE 'CREATE TABLE ut_employee AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DEL1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_DEPT_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_JOB_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_DELBY_EMP_MGR_LOOKUP AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_INS1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD1 AS
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$HIRE_DATE1 AS   
            SELECT * FROM employee';
      EXECUTE IMMEDIATE 'CREATE TABLE ut_UPD$SALARY1 AS
            SELECT * FROM employee';
   END;

I first remove all my data structures using the teardown procedure to make sure I have a clean start. Then I use dynamic SQL (the Oracle8i version) to create all my tables. I must rely on dynamic SQL because PL/SQL does not yet support native DDL statements, such as CREATE TABLE.

Then I am set to test.

Tear Down Data Structures

Well, if I am going to create a whole bunch of data structures to run my tests, I had better get rid of those structures when I am done. Here is the teardown program I generated for the te_employee package:

   PROCEDURE ut_teardown
   IS
   BEGIN
      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_employee';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DEL1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_DEPT_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_JOB_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_DELBY_EMP_MGR_LOOKUP';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_INS1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$HIRE_DATE1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

      BEGIN
         EXECUTE IMMEDIATE 'DROP TABLE ut_UPD$SALARY1';
      EXCEPTION
         WHEN OTHERS
         THEN
            NULL;
      END;

   END;

Again, I use dynamic SQL, but enclose each DROP TABLE statement inside its own exception section so that if for any reason the DROP fails, I continue on in an attempt to get as much done as possible.

Footnotes

1. These files are in the Examples directory of the utPLSQL distribution.

< Previous Section: Test a Function | Next Section: Put Test Code in Same Package >