In today’s rapidly evolving tech landscape, maintaining software quality is paramount. As a seasoned freelance developer with extensive experience in Java, Oracle, AWS, and more, I’ve seen firsthand how essential robust testing practices are for delivering reliable software. This blog post delves into the principles of Test-Driven Development (TDD) and its practical application in PL/SQL.
The Case for Test-Driven Development
Agility in software development demands flexibility, but this flexibility comes at a price. Constant improvement through continuous refactoring ensures that our product increments still meet all previous requirements. TDD is a method that helps us implement high-quality software within an agile framework by emphasizing the importance of writing tests before coding.
Key Benefits of TDD with “The only constant is change!” in mind
- Ensures Code Quality: By writing tests first, developers focus on requirements, leading to better-designed, more maintainable code.
- Reduces Bugs: Automated tests provide a safety net that quickly identifies when changes introduce errors.
- Facilitates Refactoring: With a comprehensive suite of tests, developers can confidently refactor code, knowing they can quickly verify the correctness of their changes.
Step-by-Step of Test-Driven Development
- Add a Test: Each new feature begins with writing a test based on the feature’s specifications. This approach ensures a clear understanding of requirements before implementation.
- Run All Tests: Running tests first ensures that the new test fails, validating the test suite’s effectiveness and confirming that the required behavior is not already implemented.
- Write the Code: Develop just enough code to pass the test. The focus is on fulfilling the test requirements, not on writing perfect code initially.
- Run Tests Again: Ensure all tests pass. If they don’t, adjust the code until they do.
- Refactor: Clean up the code by removing duplications and improving structure without altering functionality. This step is crucial for maintaining code quality and readability.
utPLSQL Framework
The utPLSQL framework1, modeled after JUnit, provides a robust environment for PL/SQL unit testing with features like:
- Predefined Matchers/Assertions: Simplifies writing tests with functions like `to_be_not_null` and `to_equal`.
- Automatic Transaction Control: Ensures tests do not affect the database state.
- Community Support: Active development and a wealth of additional functionalities.
Let’s look at a simple PL/SQL unit test using the utPLSQL framework. This example demonstrates a typical TDD cycle:
PROCEDURE test_add
IS
l_result NUMBER;
l_tmp NUMBER;
BEGIN
-- Step 1: Initialize objects
l_tmp := 10;
-- Step 2: Execute the operation to be tested
l_result := pkg_math.add(l_tmp, 20);
-- Step 3: Verify and compare results
ut.expect(l_result).to_be_not_null();
ut.expect(l_result).to_equal(30);
ut.expect(l_result).to_be_greater_than(l_tmp);
END;
1. Arrange – Initialize Objects:
The variable l_tmp is initialized with the value 10. This setup prepares the necessary preconditions for the test.
2. Act – Execute the Operation:
The pkg_math.add function is called with l_tmp and 20 as arguments. The result is stored in l_result. This step involves running the actual code that needs to be tested.
3. Verify and Compare Results:
The ut.expect statements are assertions used to verify the outcome:
This example illustrates how TDD in PL/SQL helps ensure that each function behaves as expected by writing tests that validate the logic before the actual implementation. By following this disciplined approach, developers can catch issues early and maintain high code quality.
Step-by-Step Guide: Installing and Using utPLSQL for PL/SQL Testing
Let’s look at a comprehensive example with the installation of utPLSQL, running the tests from the command line, and defining our test procedures.
Installation of utPLSQL
To install utPLSQL using Docker, follow these steps:
# .. preparations: get oracle hr samples
git clone https://github.com/oracle-samples/db-sample-schemas.git ./shared-docker/oracle-samples
# .. preparations: get utplsql
git clone https://github.com/utPLSQL/utPLSQL.git ./shared-docker/utplsql
# .. create and start the Docker container with "changeit" as standard passwort
docker run -e ORACLE_PWD=changeit -v /home/adam/shared-docker:/home/oracle/shared-docker -d -p 1521:1521 --name utplsql_testing container-registry.oracle.com/database/free:latest
# .. tests sqlplus connectivity
docker exec -u 0 -it utplsql_testing bash
sqlplus sys/changeit as sysdba
SQL> exit
# .. install oracle hr sample (confirm some of the questions, set changeit as hr password)
sqlplus sys/changeit@freepdb1 as sysdba @shared-docker/oracle-samples/human_resources/hr_install.sql
# .. install utplsql framework
cd shared-docker/utplsql/source/
sqlplus sys/changeit@freepdb1 as sysdba @install_headless.sql
cd ../../..
# .. create some synonyms in the hr user
sqlplus sys/changeit@freepdb1 as sysdba @shared-docker/utplsql/source/create_user_synonyms.sql ut3 hr
# .. try to connect with hr user
sqlplus hr/hr@freepdb1
SQL> select * from employees;
# .. now, everything should be installed.
In the next steps we are going to install some files into the hr schema. So lets place all the next files into the well known shared-docker/my-first-test directory.
Procedure Definition
Here’s the definition of the calc_salary_pkg.calc_sal_bonus procedure:
CREATE OR REPLACE PACKAGE calc_salary_pkg AS
FUNCTION can_get_bonus(emp employees%rowtype) RETURN BOOLEAN;
FUNCTION calc_sal_bonus(emp employees%rowtype, factor NUMBER) RETURN NUMBER;
END calc_salary_pkg;
CREATE OR REPLACE PACKAGE BODY calc_salary_pkg AS
FUNCTION can_get_bonus(emp employees%rowtype) RETURN BOOLEAN IS
BEGIN
RETURN emp.manager_id IS NULL AND emp.salary > 100000;
END can_get_bonus;
FUNCTION calc_sal_bonus(emp employees%rowtype, factor NUMBER) RETURN NUMBER IS
BEGIN
IF can_get_bonus(emp) THEN
RETURN emp.salary * factor;
ELSE
RETURN emp.salary;
END IF;
END calc_sal_bonus;
END calc_salary_pkg;
/
And let’s install the procedure into the hr schema:
sqlplus hr/hr@freepdb1 @shared-docker/my-first-test/calc_salary_pkg.sql
Test Procedures
Here are the PL/SQL test procedures to check if an employee is eligible for a bonus and to calculate the bonus:
CREATE OR REPLACE PACKAGE test_salary_pkg AS
-- %suite(Salary Package Tests)
-- %suitepath(test_salary_pkg)
-- %test(Test whether an employee is able to get a bonus)
PROCEDURE test_can_get_bonus;
-- %test(Calculation of bonus)
PROCEDURE test_calc_salary_bonus;
END test_salary_pkg;
/
CREATE OR REPLACE PACKAGE BODY test_salary_pkg AS
PROCEDURE test_can_get_bonus IS
l_employee employees%rowtype;
BEGIN
-- Initialize employee details
l_employee.manager_id := null;
l_employee.salary := 120000;
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_true();
l_employee.manager_id := 1;
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_false();
END test_can_get_bonus;
PROCEDURE test_calc_salary_bonus IS
l_employee employees%rowtype;
l_salary employees.salary%type;
BEGIN
-- Test with eligible employee
l_employee.manager_id := null;
l_employee.salary := 120000;
l_salary := calc_salary_pkg.calc_sal_bonus(l_employee, 1.5);
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_true();
ut.expect(l_salary).to_be_greater_than(l_employee.salary);
ut.expect(l_salary).to_equal(l_employee.salary * 1.5);
-- Test with ineligible employee
l_employee.manager_id := 1;
l_employee.salary := 3000;
l_salary := calc_salary_pkg.calc_sal_bonus(l_employee, 1.5);
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_false();
ut.expect(l_salary).to_equal(l_employee.salary);
END test_calc_salary_bonus;
END test_salary_pkg;
/
And let’s install our test into the hr schema:
sqlplus hr/hr@freepdb1 @shared-docker/my-first-test/test_calc_salary_pkg.sql
Now, we are able to execute our test and see if everything is valid by executing this procedure (wait a minute and I will show you that there are better ways to execute utplsql test:
alter session set current_schema=hr;
set serveroutput on
begin
ut.run();
end;
/
and the ouput of the command:
test_salary_pkg
Salary Package Tests
Test whether an employee is able to get a bonus [.131 sec]
Calculation of bonus [.043 sec]
Finished in .191238 seconds
2 tests, 0 failed, 0 errored, 0 disabled, 0 warning(s)
PL/SQL procedure successfully completed.
We see that 2 tests were run and and both were not failed (green). If we manipulate the package logic calc_salary_pkg to provoke a bug then we are going to see a quite different report output:
test_salary_pkg
Salary Package Tests
Test whether an employee is able to get a bonus [.142 sec] (FAILED - 1)
Calculation of bonus [.005 sec] (FAILED - 2)
Failures:
1) test_can_get_bonus
Actual: FALSE (boolean) was expected to be true
at "HR.TEST_SALARY_PKG.TEST_CAN_GET_BONUS", line 8
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_true();
Actual: TRUE (boolean) was expected to be false
at "HR.TEST_SALARY_PKG.TEST_CAN_GET_BONUS", line 11
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_false();
2) test_calc_salary_bonus
Actual: FALSE (boolean) was expected to be true
at "HR.TEST_SALARY_PKG.TEST_CALC_SALARY_BONUS", line 23
ut.expect(calc_salary_pkg.can_get_bonus(l_employee)).to_be_true();
Actual: 120000 (number) was expected to be greater than: 120000 (number)
at "HR.TEST_SALARY_PKG.TEST_CALC_SALARY_BONUS", line 24
ut.expect(l_salary).to_be_greater_than(l_employee.salary);
Actual: 120000 (number) was expected to equal: 180000 (number)
at "HR.TEST_SALARY_PKG.TEST_CALC_SALARY_BONUS", line 25
ut.expect(l_salary).to_equal(l_employee.salary * 1.5);
Finished in .150532 seconds
2 tests, 2 failed, 0 errored, 0 disabled, 0 warning(s)
Perfect, now we see that our recent logic bricks the calculation of the salary calculation package letting us to focus on our changes we made.
You can also check the official utplsql documentation to check how you can execute the uni tests.
This example illustrates the complete process of setting up utPLSQL, writing and executing tests, and defining the necessary PL/SQL procedures to validate and calculate employee bonuses. By following these steps, you can ensure your PL/SQL code is reliable and maintainable.
Conclusion
Testing is an inherent part of the development process. By integrating TDD with PL/SQL, you can improve software quality, maintainability, and reliability. It helps create a safety net for developers, allowing them to refactor and enhance code without fear of breaking existing functionality.
Embrace TDD to:
- Reduce development time to market.
- Increase productivity.
- Cut costs.
- Improve quality.
- Enhance flexibility and maintainability.
Remember, the best documentation for your code is a comprehensive suite of tests.
Thank you for your attention!