Automated Unit Testing of PL/SQL

by Laurence Barea (Software Development Profession)

As we moved to the adoption of CI/CD during our infrastructure and application migration to AWS, there was always a question mark surrounding Oracle and PL/SQL releases; we did not have the tools or frameworks available to allow us to deploy Oracle releases via a pipeline, which is not ideal given our goals for deployment automation are concerned.

Testing is backbone of continuous delivery model.

The problem we are having is that although many programming languages out there lend themselves to this process, Oracle databases and its procedural language, PL/SQL, does not.

Currently, there is a large Oracle footprint within the estate and a correspondingly significant amount of PL/SQL code that sits outside the CI/CD pipeline resulting in Oracle releases having to be deployed manually by DBA’s. This is an issue; delays are created, the release process is made more complex and the involvement of other teams are required.

Testing database deployment

We considered Flyway by Boxfuse and Liquibase by Datical. We decided on Liquibase and have been generally pleased with the approach it takes and how it fits in to our workflow. Flyway looked promising but would have cost $3,000 to support our version of Oracle.

Testing PL/SQL

With regards to PL/SQL we have set up a proof of concept to see a couple of possible solutions in action. Initially, we considered these tools:

• Oracle’s SQL Developer’s built in unit testing
• Code Tester by Quest (Toad)
• utPLSQL, a framework that’s been around for a few years which was originally developed by Steve Feuerstein himself, and
• ruby-plsql-spec, a unit testing framework which is built using Ruby, ruby-plsql (library) and Rspec.

… and decided on utPLSQL and ruby-plsql-spec. The former because it was more well-established and the latter because of the growing Ruby competency among our teams.

The Ruby way

The conclusion is ruby-plsql-spec works great! See the Unit Testing PL/SQL Procedures From Ruby Code for a more in-depth look.

See slides here: Unit Testing PL/SQL Procedures From Ruby Code

The utPLSQL way

We deployed utPLSQL onto a development database. Unfortunately we can’t run the database locally at the moment 😞 so we share a single development database hosted in Amazon — there’s a separate piece of work going on to resolve this.

The initial challenges surrounded taking the automated install apart and doing it manually — this is because AWS RDS does not allow file-system access and it was something we could get on with straight away rather than getting the install scripts to run remotely from the DB in question.

Working with utPLSQL is very much a manual process; there is no automation — you will have to write all the test code and you will have to maintain it.

Below is some output generated utPLSQL tests run on the HUB development environment.

Between string function
Returns substring from start position to end position [.129 sec] (FAILED - 1)
Returns substring when start position is zero [.002 sec] (FAILED - 2)
Failures:
1) basic_usage
Actual: '234' (varchar2) was expected to equal: '2345' (varchar2 at "HUB.TEST_BETWNSTR", line 5 ut.expect(betwnstr('1234567', 2, 5)).to_equal('2345');
2) zero_start_position
Actual: '1234' (varchar2) was expected to equal: '12345' (varchar2 at "HUB.TEST_BETWNSTR", line 10 ut.expect(betwnstr( '1234567', 0, 5 ) ).to_equal('12345');
Finished in .131936 seconds
2 tests, 2 failed, 0 errored, 0 disabled, 0 warning(s)

Next steps

Now that we have got both these frameworks working, we need to start incorporating them into actual work so that we can better understand how best to benefit from them.

A few points we will be looking to address as we go forwards:

• Testing across multiple databases
• Standing up and tearing down databases as part of the automated testing
• Transaction testing e.g. table data, rollbacks to savepoints etc.
• Complex type testing e.g. associative arrays of nested objects
• Integration with the code pipeline and Flyway DB.
• Exception handling.
• TDD going forwards with regards to the existing code-base.

If you enjoyed this article, please feel free to hit the👏 clap button and leave a response below. You also can follow us on Twitter, read our other blog or check us out on LinkedIn.

If you’d like to come and work with us, please check current vacancies on our job board!