Starting with the Oracle Unit Testing Framework – Part 2

This is a second part of the Oracle Unit Testing Framework post.

In the first part I’ve created Unit Test repository and prepare everything for testing.

Details you can find on the following link:

https://www.josip-pojatina.com/en/starting-with-oracle-unit-testing-framework-part-1/

 

In this part I’ll perform actual test and show some of the numerous options that you have on disposal.

I’ll be using Oracle Demo schema that you can install by using one of the available Oracle scripts.

Details of how to install sample schemas you can find on the following link:

https://oracle-base.com/articles/misc/install-sample-schemas

 

I’ve also created a demo user with ORDERS table from the OE sample schema and simple package which will INSERT / DELETE orders.

First right click on Tests to create a new Unit test (I assume you already enable Unit Test window by clicking on the View –> Unit Test).

Alternative way of creating an Unit Test is to directly right-click on the procedure inside the package, and to choose Create Unit Test as described in the following figure.

In the next step you need to choose a connection (like demo or scott) that has rights to perform Unit tests.

Instructions of how to enable users to perform Unit Tests has been provided in the first part.

In the next step you can name your Unit Test.

I used the package + procedure name.

In this example it’s ORDER_MANIPULATION.INSERT_ORDER_EXCEPTION

Besides selecting the name of the test, you can choose between the following two options:

• Create with a single Dummy implementation
• Seed/Create implementations using lookup values

First option will create a single one-off implementation of an Unit Test, while the later one will create multiple implementations based on the lookup values.

Here I’ll leave default values and click on the next button.

In the Specify Startup step, you can manage Startup actions (actions you want to perform before starting each test).

If you click on the + button, you’ll have a three options:

• None
• Table or Row Copy
• User PL/SQL Code

Here I’ll choose Table or Row Copy as shown in the above figure.

New screen will pop-up where I can choose table that will be backed up before starting the Unit Test and to publish my Startup script to Library.

By publishing to Library, next time I can just pick my Startup script without choosing teh table again.

Another benefit of publishing is your Startup script becomes available to all other developers.

In the next step – Specify Parameters, you can specify the input parameters for the test.

Here you have a bunch of options and it’s not possible to cover them all.

You can use Default under the Lookup Category, or you can create your own lookup values.

As I didn’t create my own lookup category, I’ll use the default one.

Under the Expected results you can choose between two options:

• Success
• Exception

I’ve chosen Exception under the expected output, as two input parameters have value of 9999.

Thus I’m deliberately raise an exception, as such values don’t exists in the Oracle Sample schema.

Exception number I’ve set is 20001 (custom exception handling range is from -20000 to -20999).

In the next step you can Specify Validation.

Here you can observe a new seven options that I’ll list for a sake of completeness but I’ll won’t go into the detail, as complete coverage of the Unit Test would require an entire book.

Here are options that you have:

• Boolean function
• Compare Query Results
• Compare Tables
• None
• Query returning no row(s)
• Query returning row(s)
• User PL/Sql Code

To simplify the whole process I’ll choose None and proceed.

In the last step – Teardown, there are also several options you can choose from.

• None
• Table Drop
• Table or Row Restore
• User PL/Sql Code

You can choose Table or Row Restore as we defined backup table in the first step.

As you can observe, within each option there are many sub-options you can choose from.

The last screen is a Summary where you can inspect all steps.

After pressing a Finish button, you can view all created objects in the Unit Test Window.

Now you can double click on Test Implementation 1 to open a new window.

By pressing a Run button, you can finally start the test.

As you can see, test has passed.

Here is a list of database tables where test results have been stored.

You can find them within the repo schema.

Summary:

With this article I’ve barely scratch the surface, as the whole book can be written about Oracle Unit Test.

Still I hope you get some filling of what is possible and how powerful SQL Developer, as your free interface to perform Unit Testing, actually is.

There are many more advanced topics such as:

• dynamic tests
• exporting and importing tests among developers
• version control
• SQL Developer Command Line Interface (sdcli) for test automation
• creating and running a suite of tests

I’ll leave that for some future article.

---