Starting with Oracle Unit Testing Framework – Part 1

For developers that are using Scrum methodology, it is not important to stress importance of unit tests when developing a new piece of code.

Unit tests are important for many reasons like:

• can find problems in just developed/changed code at the earliest stage (before testing team)
• are the smallest testable part of application
• cover just developed or changed piece of code
• helping with code quality assurance
• helping with security assurance
• can be automated, which is common practice
• allow developer to refactor developed code

There are also some limitations of Unit Tests.

As per definition, Unit Tests are the smallest testable part of code.

For that reason unit testing won’t catch every error in a program.

It refers to system wide errors like Integration or database errors, or errors that depends of multiple classes etc.

Performance issues won’t be captures as well.

Unit tests are very often not realistic, as scope of testing is too narrow.

It’s difficult to create Unit Test if even the smallest unit of code under testing has external dependency.

Despite of all limitations, Unit Testing is indispensable part of modern software development.

Unfortunately, you may still find a places where developers are skipping unit testing in praxis.

SQL Developer is well equipped with all necessary tools to start using unit tests.

In this blog you can find instructions how to setup Unit Test Repository, and how to setup users and prepare environment for performing unit tests.

How to actually perform Unit Test will be described in one of the following blogs.

There are several phases of Unit Tests.

• Start up actions
• Operation call
• Validation
• Teardown

1. Start up actions:

Purpose of start up actions is to make a backup of a table or to insert test data.

By leveraging start up actions I can easily overcome one of the limits of Unit Testing, and create more realistic user cases for testing.

You can choose between two options for Start up action:

• table or row copy
• user PL/SQL code

2. Operation call:

In this phase I can call procedure/function with parameters and specify whether it is expected successful call or exception.

This is the only mandatory step.

3. Validation:

In validation phase you can validate results from the previous phase.

Here you can choose one of the following options:

• boolean function
• compare Query Results
• compare Tables
• query returning no row(s)
• query returning row(s)
• user PL/SQL code
• none

4. Teardown:

In this phase you can choose:

• to restore tables affected by one of the previous phases
• to revert modifications back as result of operation call
• to delete the rows inserted in the start up action step.

After short introduction, let’s start to do actual work.

First you need to install and configure test repository which holds the necessary objects (tables, views, code + unit testing data) needed to perform unit testing.

There are two basic types of unit test repository:

• shared
• unshared

Shared repository allows you to share the test cases, test suites and test library among the team members.

On the opposite side, unshared repository, as it’s name imply, cannot be shared among multiple users/schema. It’s private repository for only one user/schema.

Although you can define multiple private repositories (e.g. one private repository for each developer user/schema), you can have only one shared repository in database.

 

Before starting make sure you can see Unit Test objects by clicking in SQLDeveloper on

View –> Unit Test

Here are the steps needed to create shared repository.

 

1. Create user/schema which will hold unit test repository

CREATE USER unit_test_repo IDENTIFIED BY unit_test_repo
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;

GRANT CREATE SESSION TO unit_test_repo;

 

2. Create repository in SQL Developer

After click on OK you’ll get the following message:

Click on Yes and the new message will pop up.

The following connections will be added to unit_test_repo user:

grant connect, resource, create view, unlimited tablespace to UNIT_TEST_REPO;

Here are the list of privileges that will be added after clicking OK on the previous figure:

grant select on dba_roles to "UNIT_TEST_REPO";

grant select on dba_role_privs to "UNIT_TEST_REPO";

By clicking on Yes, the following roles will be added:

create role UT_REPO_ADMINISTRATOR;
create role UT_REPO_USER;
grant create public synonym,drop public synonym to UT_REPO_ADMINISTRATOR;
grant select on dba_role_privs to UT_REPO_USER;
grant select on dba_role_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_ADMINISTRATOR;
grant select on dba_roles to UT_REPO_USER;
grant select on dba_tab_privs to UT_REPO_ADMINISTRATOR;
grant select on dba_tab_privs to UT_REPO_USER;
grant execute on dbms_lock to UT_REPO_ADMINISTRATOR;
grant execute on dbms_lock to UT_REPO_USER;
grant UT_REPO_USER to UT_REPO_ADMINISTRATOR with admin option;
grant UT_REPO_ADMINISTRATOR to "UNIT_TEST_REPO" with admin option;

Finally the Unit Test repository will be created, which can be confirmed by the last message.

On the Unit Test box within SQL Developer you’ll get necessary controls to work with tests.

 

3. Convert created repository into shared type so that all developers can use it.

To convert into shared repository, you need to click on the following:

Tools –> Unit Test –> Select As Shared Repository

 

4. Create user/schema for performing unit tests.

Here I’m going to clone SCOTT schema to play with unit tests.

Cloned schema name will be named: UNIT_TEST_SCOTT

To clone SCOTT schema, you need to execute the following steps:

create or replace directory test as '/home/oracle/expdp';

grant read, write on directory test to system;

expdp system directory=test dumpfile=scott.dmp logfile=scott.log schemas=scott;

impdp system directory=test dumpfile=scott.dmp remap_schema=scott:unit_test_scott

 

5. Adding users that can use just created shared repository

As I marked unit_test_repo as shared, I want to add users who can perform the unit tests.

For that reason I’ve created unit_test_scott user in the previous step.

This is how I can add unit_test_scott user to be able to use created repository.

Tools –> Unit Test –> Manage Users –> Select unit_test_repo

After clicking OK, next message will pop up:

There are two tabs on the previous figure:

• Users
• Administrators

Users can use the repository to perform Unit Tests, while Administrators can add/remove users and manage repository.

I need to add UNIT_TEST_SCOTT in the repository Users list, by selecting user I want to add and click on the right arrow.

Final result you can see on the next figure.

On the last figure you can see the content of the Administrators tab.

As the previous figure is showing, we have two repository Administrators:

• SYS
• UNIT_TEST_REPO

At this point you should have everything ready to start playing with unit tests.

---