17-2. Installing the utPLSQL Unit Testing Framework

Problem

You've chosen the utPLSQL unit-testing framework for PL/SQL for your work, and you want to install it.

Solution

First, download the utPLSQL sources from http://utplsql.sourceforge.net/. Once you have obtained the sources, use the following steps to install the utPLSQL package into the database for which you wish to write unit tests, and make it available for all schemas.

Create a user to host the utPLSQL tables, packages, and other objects. In this example, the user will be named UTP, and the default permanent and temporary tablespaces will be used.

Grant privileges to the newly created UTP user using the GRANT privilege_name TO user_name statement, replacing values with the appropriate privilege and username. The user will require the following privileges:

1. Create session
2. Create procedure
3. Create table
4. Create view
5. Create sequence
6. Create public synonym
7. Drop public synonym

Install the objects by running the ut_i_do.sql script.

Once these steps have been completed then you will have the ability to run unit tests on packages that are loaded into different schemas within the database.

How It Works

Before you can begin to write and run unit tests within the utPLSQL framework for the PL/SQL contained within your database, you must install the utPLSQL package into a database schema. While the utPLSQL framework can be loaded into the SYSTEM schema, it is better to separate the framework into its own schema by creating a separate user and installing the packages, tables, and other objects into it. The solution to this recipe steps through the recommended approach taken to install the utPLSQL framework into the database of your choice.

Once you have created a user schema in which to install the utPLSQL framework objects, you must grant it the appropriate privileges. The majority of the privileges are used to create the objects that are required to make the framework functional. Public synonyms are created for many of the framework objects, and this allows them to be accessible to other database user accounts. After all privileges have been granted, running the ut_i_do.sql script and passing the install parameter will complete the installation of the framework. After completion, you can begin to build unit test packages and install them into different schemas within the database, depending on which PL/SQL objects that you wish to test.