Unit Testing PL/SQL Code?

In almost all areas of software development, unit testing is not only common sense but also common practice. After all, hardly any serious software vendor would dare ship applications without having properly tested their functionality. When it comes to databases, many organizations still live in the Dark Ages. With Oracle SQL Developer there is absolutely no reason to remain in the dark: unit testing PL/SQL components is easy, free, and fully integrated into the IDE.

Unit Testing

Before I talk about PL/SQL unit tests, let’s take a moment to cover the role of software testing and in particular unit testing in development. Very, very few people can write bug-free code as they think of it and type, so for most of us mere mortals we need to check if what we thought the program does, really happens that way, and if what we thought the program should do really was what the customer wanted. Testing code is at the heart of software development.

There are of course several levels: unit testing, which is typically done during development and the type I shall discuss in more detail, integration, component, and system testing, which are within the purview of quality assurance (QA), and acceptance testing, which is done together with the customer. Note that unit testing does not replace functional (black-box) testing, which requires a detailed specification. Unit testing is just intended to test methods not front-ends, which is why it is often best done by the development team.

I do not want to go into details about the agile methodology, test-driven development (TDD) or even extreme programming, but what I want to mention is the TDD process because it illustrates a fundamental departure from traditional software development:

1. Write a test case for the new feature or to expose a bug.
2. Run the tests to see whether the new test case fails.
3. Write code for the new feature or to fix the bug.
4. Run the tests again to see that they succeed.
5. Refactor the code.
6. Repeat.

Many developers, especially database developers, start by writing code. TDD offers an alternate view that is very much in line with the scientific method: if you cannot prove your theory wrong, then your theory is not scientific. Steps 1 and 2 embody exactly that principle: first prove that you have a test case that fails. Once you have done that, attempt to solve the problem (Step 3), prove that you have solved the problem (Step 4), make the solution more tractable and ensure you stick to existing code standards (Step 5), and finally make sure that the cleaned up solution still solves the original problem and you haven’t broken anything along the way (Step 6).

Another feature of such automated tests is that you make the entire process repeatable. What worked yesterday need not function properly today. Virtually all unit test frameworks allow you to see code coverage statistics, so you are able to identify bits and pieces of the code that you rarely ever reach, which allows you to write more detailed test cases that expose these (almost) unreachable areas that could potentially blow up in your team’s face once the application nears release. Of course, exhaustive testing is impossible.

A note of caution: DBMS_OUTPUT.PUT_LINE does not constitute a test. You can use it to display intermediate output as you write and debug your code, but it does not satisfy the basic requirements for unit tests: it is not easily automated and therefore not repeatable.

The Business Case For Unit Testing

According to a survey by Red Gate, 24% of developers do not use database source control, yet 70% of developers who use agile methodologies do unit testing.

‘So what?’ may be your reaction to these figure, ‘Why should I care? I’ve managed thus far.’

Well, let’s take a look at some more statistics: a mere 10% of all software defects causes 90% of downtimes!

‘Still, that’s not a reason to force me to unit test PL/SQL objects.’

You sure are a tough sell, but fortunately there is more. According to studies by George & Williams (2003) and Nagappan et al. (2008) , unit testing decreases pre-release defects 45-90% while only requiring 15-35% more initial effort.

‘I don’t have 15% more time, so forget it!’

Your company (and its clients) can save serious cash, because the cost of fixing bugs after release is 2x higher than during QA and 15x higher than during development. Because unit tests are typically conducted by developers before handing over the code to the QA team, you can thus reduce the number of software defects, save money, reduce downtimes, and thus increase customer satisfaction and decrease the time to market.

That, methinks, should be reasons aplenty.

Unit Testing PL/SQL Code

PL/SQL is Oracle’s own extension of their particular interpretation and implementation of SQL to provide procedural constructs, constants and variables, subprograms, and error handling methods to the Oracle Database, or in their own words:

PL/SQL is an imperative [language] that was designed specifically for the seamless processing of SQL commands. It provides specific syntax for this purpose and supports exactly the same data types as SQL. Server-side PL/SQL is stored and compiled in Oracle Database and runs within the Oracle executable.

SQL and PL/SQL are both programming languages, but the main difference is that SQL is declarative whereas PL/SQL is imperative. Stated differently, with SQL you tell the database what you want and it decides how to get it, but with PL/SQL you tell the database exactly how you want to retrieve the results you’re after. PL/SQL is closer to ‘standard’ programming languages (C#, Java, Python, …) because they are all third-generation languages (3GL). SQL is a 4GL.

That last statement is not supposed to be an excuse for not having source control though, although some database developers I have met and worked with do not deem it necessary to have a complete history of all database objects; they have a bunch of files with deployment scripts. Whether they include these scripts in revision control varies from one developer to the next. I tend to agree with the DevOps philosophy, which says that all components have to be included in your continuous delivery strategy and thus in your source code repository. There are several vendors who offer revision control for databases, for instance Datical, DB Maestro, Liquibase, and Red Gate, but the adoption of their tools is not as widespread as it could or perhaps even should be. There’s also the more hardcore TSTATS approach, developed by Oracle wizard Tony Hasler and described in Expert Oracle SQL, which posits that even objects statistics need to be included when moving from development to production, lest the database decides to suddenly change the execution plan and thereby negatively affect the runtime performance.

Queries (in views) are typically not easy to unit test because the state of the database changes all the time. Fortunately, data is commonly exposed through an API that is written in PL/SQL or another 3GL, for which you do have a few tools at your disposal. I have listed the ones that I know about in alphabetical order below.

Best known in the Oracle community are perhaps Quest’s Code Tester, especially with people who work with Toad, utPLSQL, which has been developed by PL/SQL superman Steven Feuerstein, and Oracle SQL Developer’s built-in features.

• DbFit
• Oracle SQL Developer
• PL/SQL Unit Testing for Oracle (PLUTO)
• Quest Code Tester for Oracle
• ruby-plsql-spec
• utPLSQL

I want to talk about what Oracle SQL Developer has to offer because a) I think the built-in capabilities are really nice, b) I’m a big fan of SQL Developer, and c) it’s completely free.

Oracle SQL Developer

Jeff Smith, the Product Manager for SQL Developer, has already written about the unit testing features that reside in SQL Developer. There is also extensive documentation available in the Help menu, which I have summarized for your convenience.

First off, you need to set up a repository on an existing database. In case you have no privileges to do so or the DBA is reluctant, you can simply download Oracle XE on your local machine and install the repository there. The important point to take home is that the repository does not have to be on the same database.

If you want the entire development team to access the same repository, which is not a bad idea, you can create a shared repository. Whether you install that repository on a machine hosting the express edition that everyone can access remotely or on an actual database is up to you and depends on the infrastructure you have in place. The advantage of a shared repository is that if someone commits changes to the code base, you can run the same unit tests again to verify that the team’s code still works as intended, including the new component. The notion that ‘it worked yesterday, so it’ll probably work today’ does not hold water. With a repository you don’t have to rely on mere hope: you can be sure! Well, at least as sure as your unit tests allow you to be.

A unit test is a group of test cases, or implementations, on a specific PL/SQL object. This means that you can verify the functionality of, say, a function in various ways. You can even check for exceptions; SQL Developer allows you to check whether any exception was thrown or a very specific error number. You typically want the unit test to be named after the object it is supposed to test. Functions and procedures in packages are automatically qualified by the package name. If you happen to have more than one unit test for an object, you can add meaningful suffixes like so: MY_PROCEDURE#SOME_SUFFIX. You have 120 bytes for the entire name.

Implementations are named automatically (Implementation 1, Implementation 2, etc.) but it is recommended that you give meaningful names to each implementation. Each implementation can have its own start-up and teardown process, which can be either custom PL/SQL code or a table or row copy and table or row restore, which can be used to restore data to the state before the test ran. All you have to do is specify the name of the temporary object into which the data is stored, and SQL Developer will take care of the rest; if there is already an object with that name, it will be overwritten.

A unit test runs implementations sequentially, where each implementation consists of a start-up, test case, and teardown. The start-up and teardown are optional.

A bunch of unit tests can be grouped into a test suite. Test suites can have start-up and teardown processes too, just like implementations. It is also possible to group test suites into a so-called supersuite. This supersuite can be used to run the suites one after another. It is recommended that you create a test suite for each functional area.

SQL Developer also has a unit test library that contains common actions. It allows you to store dynamic value queries, start-up, teardown, and validation actions. When you create an action in a unit test, you can simply use the publish to library option to save time.

Frequently used values can be defined by category and data type in lookups. All but data values can be stored here, so you can pick from the correct range of values in all unit tests. You can of course use manually provided values each time but it’s easier to define ‘interesting’ values once and them select the ones you want to include in the implementation.

A test case would not be called that without some process validation. What and how you want to test the output of a PL/SQL object is up to you, but SQL Developer has most bases covered:

• Boolean function
• Compare query results
• Compare tables
• Query returning no rows
• Query returning row(s)
• User PL/SQL code

If you go for query returning rows, you have to supply the SELECT statement that gives back the result after running the PL/SQL object. This option comes in handy when you use a dynamic value query to obtain live (sample) data from an existing table, as it allows you to check reasonableness, that is whether a value is in a specified range.

It is also possible to use parameters in the process validation:

• IN parameter: {param_name}
• OUT parameter: {param_name$}
• return value: {RETURNS$}

Character parameters or return values can be used in WHERE clauses but they have to be quoted like so: WHERE col_name = '{param_name}'.

For each implementation you can tick a box to gather code coverage figures. The corresponding statistics will be included in the reports, where you can see the details of each run at each level: suite, unit test, and test case.

When you create objects for testing purposes, make sure that they are created by the user who will run the tests. Users who can work with the repository must have the UT_REPO_USER role assigned to them. The UT_REPO_ADMINISTRATOR role is of course for the administrator who can assign roles to users.

Finally, I would like to mention that not all is okely dokely. There are probably more restrictions but from my own fiddling around I have found that SQL Developer does not support (nested) TABLE types as parameters or return values. It does, however, allow unconstrained character return values (e.g. VARCHAR2) but it complains that the tests may not execute or test correctly.

All in all, I hope I have been able to convince you that unit testing PL/SQL objects in SQL Developer is possible, free, easy, and critical to the quality of your code.