< Previous Section: Glossary and Requirements | Next Section: Administrative Topics >

The Four Step Program to Using utPLSQL

Step 2. Choose a program to test and identify the test cases.

A note on which schemas to use

Step 1. Install (and Upgrade) utPLSQL.

Note: if you have already installed a previous version of utPLSQL, you will use these same steps to perform your install. The installation procedure does not remove any objects, such as tables, prior to installation. If you wish to install a fresh copy of utPLSQL, and not upgrade over the existing installation, please follow the steps below for removing utPLSQL.

Connect via SQL*Plus to the session that will own the utPLSQL components. If you do not already have a schema defined, then you must create it. The utPLSQL schema must have the authority to:

Create a session.
Create tables, views, packages and sequences.

If you like, you can install utPLSQL into the SYSTEM schema, which will avoid the need to create a new user. However, you may prefer to keep everything in a separate place. The following is an example script submitted by Bill Pribyl, which creates a user "UTP" with sufficient privileges to install utPLSQL. Obviously it is only an example and will need to be changed for your environment:

connect system/manager
create user utp identified by utp default tablespace
  users temporary tablespace temp;

grant create session, create table, create procedure,
  create sequence, create view, create public synonym,
  drop public synonym to utp;

alter user utp quota unlimited on users;

Note If the schema in question does not have the ability to create and drop public synonyms or execute privilege on DBMS_PIPE, you may get error messages when installing. However, utPLSQL will still function correctly.

Once you have connected to the schema, run the ut_i_do.sql file with the parameter "install" to install all utPLSQL objects. You should ensure that the working directory of your SQL*Plus session is the directory holding the utPLSQL files, then issue this as follows:

SQL> @ut_i_do install

This file will create all tables, packages and other objects needed. Note that the installation script creates some files dynamically using the SPOOL command. For this reason, it is necessary that you have write permission in the directory.

To check the installation of utPLSQL, examine the ut_i_install.log file.

Removing utPLSQL

To de-install the product, run the ut_i_do.sql script again, but with the parameter "uninstall", as in:

SQL> @ut_i_do uninstall

Step 2. Choose a program to test and identify the test cases.

You may want to test a single stand-alone procedure or function, or a set of programs in a package. Pick the program and then come up with the set of different cases you want to test. This data will determine what kind of and how many tests you run for your program.

Suppose, for example, that I have created a stand alone function called betwnStr (a variation on SUBSTR that returns a sub-string based on a starting and ending location) that is stored in betwnstr.sf (1):

CREATE OR REPLACE FUNCTION betwnStr (
   string_in IN VARCHAR2,
   start_in  IN INTEGER,
   end_in    IN INTEGER
)
RETURN VARCHAR2
IS
BEGIN
   RETURN (
      SUBSTR (
         string_in,
         start_in,
         end_in - start_in + 1
      )
   );
END;

To test this function, I will want to pass in a variety of inputs, as shown in this table:

Start	End	Result
NULL	NOT NULL	NULL
NOT NULL	NULL	NULL
NULL	NULL	NULL
3 (positive number)	1 (smaller positive number)	NULL
3 (positive number)	100 (larger than length of string)	Remainder of string from 3

So now I know what I want to test and how I want to test it.

Step 3. Build a test package.

utPLSQL offers an easy, automated way to run your tests. To work automatically, though, you have to follow some rules so that utPLSQL can find and execute your test code. Here are the rules:

The test code must be placed inside a test package.

The test package specification should be stored in a file named ut_<program>.pks and the body must be stored in a file named ut_<program>.pkb (by following this naming convention, utPLSQL can be set to automatically recompile your test package before each test).

The test package must contain a setup procedure called ut_setup and a teardown procedure called ut_teardown, neither of which take any arguments.

The test package should have a separate procedure for each program to be tested in this package.

Now, you should know that there are a number of bells and whistles in utPLSQL that allow you to change many default values (such as the prefixes used for the setup, teardown and test procedures) and behavior of the utPLSQL packages. While you are "Getting Started", however, we will rely completely on the defaults and get you up and testing ASAP.

So if I am going to test the stand-alone procedure, betwnstr, my test package specification, saved in ut_betwnstr.pks(1), will look like this:

CREATE OR REPLACE PACKAGE ut_betwnstr
IS
   PROCEDURE ut_setup;
   PROCEDURE ut_teardown;
   
   PROCEDURE ut_betwnstr;
END ut_betwnstr;
/

Now let's build the package body, saved in ut_betwnstr.pkb(1). In this very simple case, I don't have to set up any data structures and I do not, therefore, have to tear anything down. My teardown procedure can be empty (but it must be present). So I have:

CREATE OR REPLACE PACKAGE BODY ut_betwnstr
IS
   PROCEDURE ut_setup IS
   BEGIN
      NULL;
   END;
   
   PROCEDURE ut_teardown
   IS
   BEGIN
      NULL;
   END;

Time to build the unit test procedure. To do this, I need to go back to my grid of test cases and translate those sets of data inputs and results into calls to programs in the utAssert package.

utAssert offers a number of "assertion routines" that test the values or expression you pass to them and then record the results in utPLSQL. You can, with utAssert, test for equality between two strings or files or tables or collections. You can test to see if an expression evaluates to NULL. I can use both of these types of assertions (equality and IS NULL) for my test cases, which I repeat below:

Start	End	Result
NULL	NOT NULL	NULL
NOT NULL	NULL	NULL
NULL	NULL	NULL
3 (positive number)	1 (smaller positive number)	NULL
3 (positive number)	100 (larger than length of string)	Remainder of string

Here's how it works: for each test case, I provide a string description of the case, then the expression I want to evaluate. Let's start with "typical valid usage". I pass a string "abcdefg", a start location of 3 and end location of 5, and betwnstr should return "cde". I express that in my unit test procedure as follows:

PROCEDURE ut_betwnstr IS
BEGIN
   utAssert.eq (
      'Typical valid usage',
      BETWNSTR(
         STRING_IN => 'abcdefg',
         START_IN => 3,
         END_IN => 5
      ),
      'cde'
   );

Notice that I call utAssert.eq because I want to compare the value returned by betwnstr with the string "cde". They should be equal.

I can now write another call to a utAssert program for each of my cases. In this very next example, I call utAssert.isnull, because I am expecting betwnstr to return a NULL value.

      utAssert.isnull (
         'NULL start',
         BETWNSTR(
            STRING_IN => 'abcdefg',
            START_IN => NULL,
            END_IN => 5
         )
      );
      
      utAssert.isnull (
         'NULL end',
         BETWNSTR(
            STRING_IN => 'abcdefg',
            START_IN => 2,
            END_IN => NULL
         )
      );
      
      utAssert.isnull (
         'End smaller than start',
         BETWNSTR(
            STRING_IN => 'abcdefg',
            START_IN => 5,
            END_IN => 2
         )
      );
      
      utAssert.eq (
         'End larger than string length',
         BETWNSTR(
            STRING_IN => 'abcdefg',
            START_IN => 3,
            END_IN => 200
         ),
         'cdefg'
      );

   END ut_BETWNSTR;
   
END ut_betwnstr;
/

I have now created my unit test program for the betwnstr function. I will compile both these files to make sure there are no compile errors:

SQL> @ut_betwnstr.pks

Package created.

SQL> @ut_betwnstr.pkb

Package body created.

Note: when you run your test, utPLSQL will by default attempt to recompile your test package to ensure that the latest changes are incorporated into the test. It is still worth doing an initial compile to make sure you built your test properly. You will also need to make sure that UTL_FILE is installed and configured so that your test package files can be read and compiled by utPLSQL.

So with the test package in place and compiling, now let's see how we go about running the test.

Step 4. Run your test.

You've built your code, you've built your test package, you've compiled that test package. Now it's time to run the test. Start up SQL*Plus and connect to the schema owning the code you want to test.

Then run your test package within the utPLSQL testing framework by calling utPLSQL.test:

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)

That second parameter in the call to utplsql.test, "recompile_in => FALSE", tells utPLSQL that you have already compiled your test package. You can also have utPLSQL automatically recompile your test package each time you run a test.

If the test does not find any errors (which means that the assertion programs did not detect any conflicts), you will see this output:

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)
SUCCESS: "betwnstr"

If the test detected a failure, you will see output along these lines:

SQL> exec utplsql.test ('betwnstr', recompile_in => FALSE)
FAILURE: "betwnstr"
BETWNSTR: IS NULL: NULL start
BETWNSTR: End larger than string length; expected "cdeg", got "cdefg"

As you can see, utPLSQL tells you the description of the test case that failed, and also shows you as much as it can about what caused the failure.

You have now successfully installed utPLSQL, written a test package and run your test!

Automatic Recompilation of Test Package

utPLSQL will, by default, attempt to recompile your test package code (which must be put in two files <name>.pks for the package specification and <name>.pkb for the package body). This of course assumes that the files are situated on the same machine as your database. If this is not the case, you can turn off this functionality by calling utConfig.autocompile as follows:

utConfig.autocompile(false);

If you do wish to use this functionality, utPLSQL needs the UTL_FILE package provided by Oracle to read the source code files and then compile the code found in those files. Before using UTL_FILE you must configure it for use from within PL/SQL. Once you have confirmed that UTL_FILE works in your database instance, you must tell utPLSQL where the test package is located by calling utPLSQL.setdir. If you do not do this, then utPLSQL will not be able to recompile your test package before each run, and instead will display an error message.

Call the utConfig.setdir program to tell utPLSQL the location of your source code. Suppose that I stored all my code in e:\utplsql\testall. Then I would make this call in SQL*Plus:

SQL> exec utplsql.setdir ('e:\utplsql\testall')

A note on which schemas to use

In step 1, above, we described which user should own the objects which make up the utPLSQL framework. However, there has often been confusion about which schema should contain the test packages and which schema to connect as when running the tests. There are many ways to do it, but the simplest is as follows:

It doesn't matter which schema owns utPLSQL itself, so long as other users have access to it.
The test packages should go in the same schema as the code that is being tested.
You should connect as the user who owns the test packages (and hence the tested code) when running the tests

Where to go from here

If you proceeded through all four steps, you should now have used utPLSQL successfully to test a very simple function (betwnstr) or your own functionality. This will undoubtedly leave you very excited about using utPLSQL to handle much more complex code and elaborate testing requirements.

To find out more about the different features and functionality available in utPLSQL, visit the User Guide.

To read through a more thorough presentation of how to build test packages in utPLSQL, visit How to Build Test Packages.

To see a wide array of examples of building test cases and different kinds of test packages, visit the Examples document.

Footnotes

1. This file is to be found in the Examples directory of the utPLSQL distribution.

< Previous Section: Glossary and Requirements | Next Section: Administrative Topics >

The Four Step Program to Using utPLSQL

Step 1. Install utPLSQL.

Step 2. Choose a program to test and identify the test cases.

Step 3. Build a test package.

Step 4. Run your test.

A note on which schemas to use

Where to go from here

Step 1. Install (and Upgrade) utPLSQL.

Removing utPLSQL

Step 2. Choose a program to test and identify the test cases.

Step 3. Build a test package.

Step 4. Run your test.

Automatic Recompilation of Test Package

A note on which schemas to use

Where to go from here

Footnotes