In this article we’ll discuss some of the best practices of unit testing.

What is unit testing?

Unit testing in Toad for Oracle is used to test PL/SQL objects (functions, procedures and packages). A unit test is the actual code that tests the database objects. A unit test consists of one or more test cases, or implementations, with a default implementation. A group of unit tests is called a test suite. Unit tests use annotations for configuration. Unit tests are based on expectations to test if a given PL/SQL code functions as it is designed to, or expected to. An expectation consists of an expected value, an actual value and a comparison operation to run. A test passes if the actual value is equal to the expected value or validates using the specified comparison operator.

Toad for Oracle unit testing functionality is accessible from the toolbar as shown in Figure 1.

 

Figure 1. Toad for Oracle Unit Testing

Install the latest utPLSQL

The unit testing in Toad for Oracle is based on the open source framework utPLSQL. A utPLSQL unit test is a PL/SQL procedure itself. Install the latest version of utPLSQL. Select Unit Testing>Install utPLSQL. In the Options window, keep the default settings which include Accessibility and DDL Trigger. By default, Accessibility is set to All schemas may use. Alternatively, specific schemas may be selected. By default, Install DDL Trigger checkbox is selected, which allows compile-time analysis of unit test suites for faster framework startup time, especially for large schemas. Click on Next. In Review click on Install.

After an Installation completes successfully, a message dialog should get displayed. Click on OK. The View log may be used to verify that no errors are generated. If the latest utPLSQL is already installed, selecting Unit Testing>Install utPLSQL displays dialog, shown in Figure 2.

 

Figure 2. utPLSQL is already installed

Unit tests test one thing only

The reason unit tests are called so is because a single unit test will test one thing (a unit of code) only. Unit tests should not be designed to test multiple database or business logic behaviors in the same test.

Create dependencies ahead of developing the unit tests

Create the dependencies, such as a database table, PL/SQL procedure and function before developing the unit tests for them. A commonly used example is of using an EMPLOYEEStable along with an AWARD_BONUSprocedure to calculate the bonus to award, and add to salary, for different employees based on the sales they generate.

A function to get an employee’s salary may be as follows:

CREATE OR REPLACE FUNCTION get_salary(

   emp_id NUMBER

)

RETURN NUMBER

IS

   salary NUMBER := 0;

BEGIN

   — get salary

   SELECT salary

   INTO salary

   FROM employees

   WHERE employee_id = emp_id;

 

   — return the salary

   RETURN salary;

END;

Run the PL/SQL code in Toad for Oracle Editor to create the function as shown in Figure 3.

Figure 3. Creating a Function

The GET_SALARY function, and the AWARD_BONUS database, objects should get listed in the Tested Object drop-down when creating a new unit test as shown in Figure 4.

 

Figure 4. Database Objects to create a unit test for should get listed

Use a test suite

Group related unit tests in the same test suite to manage the unit tests. To create a new test suite, select Unit Testing>Unit Test Manager as shown in Figure 1. Click on Continue in the Introduction window, or if a Continue button is not displayed, select Enter. In the Unit Test Manager view, click on Create new test suite as shown in Figure 5.

 

Figure 5. Create new test suite

If Unit Testing>Create Unit Test is selected in the toolbar and no test suite exists, the user is prompted to create one as shown in Figure 6.

Figure 6. Create new test suite

Isolate tests

Separate tests from the PL/SQL code they test, and from other tests in another test suite. Use a version control system to version tests.

Add a test description

To be able to identify the purpose of a test later, add a test description, as example, “Test to set upper limit of bonus”, as shown in Figure 7.

Figure 7. Add test description

Use the code editor

The Create Unit Test wizard is for Preview only as indicated by the message in Figure 8.

Figure 8. Preview only

Click on Send to Editor as shown in Figure 9 to send the PL/SQL code to the Editor window.

Figure 9. Send to Editor

Click on Continue in Create Unit Test – Next Steps. In the Editor, set initial values for variables, as example:

— Variable initializations

       l_EMP_ID := 1001;

       l_SALES_AMT := 5000;

Specify the database objects, such as procedures and functions to call in the Editor itself, providing the procedure arguments in the call, as example:

— Call

       ADMIN.AWARD_BONUS (EMP_ID => l_EMP_ID, SALES_AMT => l_SALES_AMT);

Specify expectations in the Editor. Expectations compare actual values from the tested code against expected values. The test passes when all expectations evaluate to True. Examples:

       ut.expect(actual_value).to_equal(expected_value);

   ut.expect(actual_value, 'optional failure message').to_be_null;

Click on Execute Script (Figure 10) in the Editor to run the script to create the PACKAGE, and PACKAGE BODY for the unit test.

Figure 10. Execute Script

Follow the 3A rule

Adhere to the 3A rule of PL/SQL unit testing:

• Arrange
• Act
• Assert

Arrangeimplies that a user is expected to set up the inputs, data and environment for the unit tests. Act is for running the unit test code. Assert is validating or verifying that the tests passed.

So that a test leaves the environment as it found it, you should generate the subprogram stubs to set up and tear down the test suite and the tests as shown in Figure 11.

Figure 11. Generate subprogram stubs

Unit Tests are expected to set up the database state they need ahead of developing the unit tests themselves.  

It is best to compile the unit tests before running them. Right click on a test suite in the Unit Test Manager and select Compile, or Compile All as shown in Figure 12.

Figure 12. Compile unit tests

Compilation errors if any get displayed in the Messages as shown in Figure 13.

 

Figure 13. Compilation errors

Verify that the tests run as expected. Click on Run selected unit tests (Figure 14) in the Unit Test Manager to run one or more tests.

Figure 14. Run selected unit tests

If the unit tests pass, the Finished: Success message gets displayed as shown in Figure 15.

 

Figure 15. Verify Unit tests pass

Design tests to fail

Design unit tests so that they fail if the expected condition, or expectation, is not met. A test such as the following that validates that 1 is equal to 1 never fails, and is therefore of no use:

ut.expect(1).to_equal(1);

Failed tests can be run again after fixing the database object, the data or the expectation. Right-click on a failed test and select Run Failed Tests (Figure 16).

Figure 16. Run Failed Tests

Do not include logic in tests

A unit test only tests the value of a variable and should not define any code logic. As example, the following expectation only tests that the salary of an employee with a specified id is 9400.

ut.expect(admin.get_salary(EMP_ID => l_EMP_ID)).to_equal(9400);

Toad for Oracle provides several built-in code snippets to use in unit testing. Select View>Code Snippets in the toolbar. Select Code Snippets category as Unit Testing to list the code snippets (Figure 17).

 

Figure 17. Unit Testing Code Snippets

Keep tests short

The unit tests should be short and should not have unnecessary code. Avoid defining variables that are not referenced inside the program, as indicated by a message in Figure 18.

Figure 18. Avoid defining variables that are not used

Maintain tests

Maintain unit tests, and debug them as the database objects, data and expectations evolve. Develop multiple test implementations as test cases are useful in charting how a unit test has evolved. Fix Warning messages as they get generated; some shown in Figure 19.

Figure 19. Warning messages

Test naming

Unit tests should be named to avoid conflicting, or similar, names so that it is easier to distinguish between them. Unit test names that are reserved words or utilize special characters must be double quoted as indicated by a message in Figure 20.

Figure 20. Unit test name may require double quotes

Run multiple tests for same database objects

Develop and run multiple unit tests for the same database objects to test values of different variables, using different comparison operators. As an example, one test could test that an employee’s salary equals a specified value.

ut.expect(admin.get_salary(EMP_ID => l_EMP_ID)).to_equal(9400);

A second test could test that an employee’s salary should not exceed a specified value:

ut.expect(admin.get_salary(EMP_ID => l_EMP_ID)).to_be_less_than(10000);

To add a new test to an existing test suite, click on Add new test to suite (Figure 21) in Unit Test Manager.

Figure 21. Add new test to suite

To run multiple unit tests together, right-click in Unit Test Manager, and select Run All Unit Tests (Figure 22).

Figure 22. Run All Unit Tests

Click on Refresh in the Unit Test Manager after adding new tests in the Editor. Results for multiple unit tests are shown in Figure 23.

Figure 23. Result for multiple unit tests

Rerun test each time code changes

Run the same test again after the database object, data or unit test code changes. To run the same test again, right-click on the test in the Unit Test Manager and select Repeat Last Run (Figure 24).

Figure 24. Repeat Last Run

Use reports to display test results

To create a report for a unit test result, click on View Reports (Figure 25) in Results.

Figure 25. View Reports

In View Reports click on Export to export a report to one of the supported formats such as .html, and .txt.

In this article we discussed some best practices for running PL/SQL unit tests in Toad for Oracle.

What’s Next?

1. Explore and Select a Toad product at https://support.quest.com/download-product-select
2. Toad for Oracle download Free Trial Versionfrom https://www.quest.com/register/54618/
3. Buy Online or Request Pricing for Toad for Oracle at https://www.quest.com/products/toad-for-oracle/
4. Get Product Supportfor Toad for Oracle at https://support.quest.com/

Have questions about Toad Developer Tools? Click Start Discussionand this blog topic will be transferred to the Toad World Forums.

Try Toad free for 30 days.  Free 30-day Trial Already in a trial? Talk to sales or buy now online. Already a loyal fan of Toad for Oracle? Renew now.

 

Related Links

Blog: Using McCabe's Cyclomatic Complexity to design unit tests in Toad for Oracle 

Blog: Quickly Deploy High-Quality Code with utPLSQL [Webcast] 

Blog: 5 fitness tips for PL/SQL code 

Blog: Why can’t database ops align with DevOps processes? 

Blog: Create a simple PL/SQL unit test using Toad 

 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.