Actually, what is a unit test? It's "something" that tests automatically if a piece of code (function, procedure, process, ...) really works as expected. A primitive solution might be an anonymous PL/SQL block, but Kent Beck (author of the xUnit framework, that inspired the popular JUnit) introduced three phases of a unit test - startup, the unit test itself and a teardown.

• startup - preparation of the environment (data, object instances, ...)
• unit test itself - execution of the tested code, validation of results
• teardown - cleaning the environment (data removal, releasing resources, ...)

and as we'll see later, SQL Developer respects this structure when writing unit tests.

Once you have a unit test defined, you have to store it somewhere - SQL Developer uses a "repository" which is just an Oracle schema with a predefined set of tables. So a developer actually works with two types of schemas - one or more schemas used to develop PL/SQL code, and a schema used to store unit tests (indirectly, as SQL Developer does all the work).

Demonstration of a simple unit test

Let's create two users - DEVEL_SCHEMA and UTREPOS_SCHEMA with usual rights (and corresponding connections in SQL Developer). The first schema will be used to develop PL/SQL code, the other one to store unit test definitions.

Now create a simple PL/SQL function "REPEAT" that repeats the string (number of repeats is passed in the second parameter), and it's unit test. Implementation of the function is quite simple:

Now we'll reate a unit test for this function - but first we have to define a unit test repository, i.e. tell the SQL developer to use UTREPOS_SCHEMA to store unit test definitions (more precisely, the corresponding connection "utrepos"). You can do that using "Tools -> Unit Test -> Select Current Repository ..." menu item.

From the list of connections select the one corresponding to UTREPOS_SCHEMA (in my case the connection is called "utrepos"), SQL Developer will find out it's an empty schema (does not contain the necessary tables) and offers to create the repository (tables etc.). It will offer to grant all necessary rights if needed (the user does not have them).

Now you have a unit test repository and SQL Developer knows about it, so let's create a unit test for the PL/SQL function. The test will do a very simple thing - call the function with some parameters and check it returns an expected value.

Right-click on the function name and then choose "Create Unit Test" from the menu.

This will start a unit test wizard - several simple screens with various unit test options - name (choose for example "REPEAT_TEST"), startup, teardown and validations (we won't use them so leave them empty), and finally parameters used when calling the function and the expected return value.

I've set the string "abc" and number of repetitions to "3" so the expected value is "abcabcabc." After clicking "Finish" (the GUI is obviously somehow confused by the czech language, so it displays "Dokoncit" which is czech translation of "Finish"), the unit test is created in the repository. If you want to display (and execute it), click on the "Unit Test" tab above the connections - you'll get a listing of the repository, i.e. a list of unit tests and test cases (and other objects we're not interested in).

After clicking the unit test you'll see it's details (notice the expected return value is empty and contains "(null)" instead of the value specified when creating the test - this is a bug, so set it again or the test will fail when executed).

And of course, you may run the test using the green arrow.

The results of a unit test are displayed in very nice form

This concludes the short unit test demonstration.

Conclusion

What you should keep in mind is that this is a very simple unit test, and the question how this solution works in more complex cases. My experience from practical use are that there are multiple bugs or even worse features that make it quite complicated to define unit tests, e.g.

• binding to separate functions / procedures (each test corresponds to one function or procedure)
• binding to a schema / impossibility to switch to another one
• impossible to debug the test
• problems when renaming the tested methods
• storage in XML format (import / export)
• no space for a unit test description
• different behavior when executed from GUI / command line (commit / rollback)
• you need SQL Developer to execute unit tests (bound to connections)

You'll find details about these issues in the following article.