Jun 1, 2022 3:00:00 AM by Deepak Vohra
In this article we’ll discuss some of the best practices of 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
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
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 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 EMPLOYEES table along with an AWARD_BONUS procedure 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(
salary NUMBER := 0;
-- get salary
WHERE employee_id = emp_id;
-- return the salary
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
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
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.
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
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:
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, '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
Adhere to the 3A rule of PL/SQL unit testing:
Arrange implies 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 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:
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
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
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 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
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
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
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
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.
Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.
Try Toad free for 30 days.
Already in a trial? Talk to sales or buy now online.
Already a loyal fan of Toad for Oracle? Renew now.
Head over to the Toad for Oracle forum on Toad World®! Chat with Toad developers, and lots of experienced users.
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.
Tags: Toad for Oracle
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.