Unit Testing Framework for Oracle Pl/Sql

Unit Testing Framework for Oracle PL/SQL?

The most commonly used is probably utPLSQL

The original author of this toolkit now works for Quest, which has a commercial PL/SQL unit testing application.

Unit testing for PL/SQL

I use utPLSQL as the framework and OUnit as the client. utPLSQL isn't really meant to be used by itself, a good graphical client is required. OUnit is the predecessor to Qute. Qute is also a good tool but more complex than my requirements - it allows you to construct tests using a GUI and does good stuff like test code generation.

Edit: My understanding is that utPLSQL stores all results in database tables, including all historical results which would make a good data source for gathering statistics for continuous integration. You can also define test groups so a single call to utPLSQL can call multiple test packages.

Unit testing with Oracle

There are a couple of options. Oracle SQL Developer has a built-in test suite but that's no use for people using other IDEs such as PL/SQL Developer, and also not helpful for CI purposes.

The best choice is UTPLSQL, which was originally written by the renowned Steven Feuerstein. This is pure PL/SQL. It is pretty good and free open source software.

The really good news is that - after a long period of hibernation - UTPLSQL is back in active support. Version three is a complete re-write, with clever support for readable tests: it's a lot more like the JUnit and NUnit style of unit testing. Excitingly there are hooks for running UTPLSQL tests - including rendered output - in CI tools like TeamCity, Jenkins, etc. There is also a community of developers writing helpful extensions, such as a SQL Developer plugin.

There's a migration tool for v2 tests.

---

Author's note: I have completely re-written this answer to reflect the fact that UTPLSQL is now an active product again.

PL/SQL package automated testing

I don't get your "simple test in complex test suite". However if your main testing scenario is:

1. Set up test data in database table(s)
2. Run a PL/SQL subprogram
3. Verify the subprogram modified data in database table(s) correctly

I have a good news for you - http://dbfit.github.io/dbfit/ is a great tool for that kind of testing. I have used it in several occasions and I'm very happy with it.

Oh, and one can't access the package's private subprograms. You can only access package's public interface (the specification).

How to (unit-)test data intensive PL/SQL application

There are several different test tools for PL/SQL out there. Steven Feuerstein has written two of them, utplsql and Quest Code Tester for Oracle (formerly QUTE). I am a big fan of utplsql, but it no longer has an active support community (which is a shame). It also tends to be quite verbose, especially when it comes to setting up test fixtures. It does have the cardinal virtual of being pure PL/SQL packages; the source code is a bit gnarly but it is FOSS.

QCTO comes with a GUI, which means - like other Quest products i.e. TOAD - it is Windows only. It doesn't exactly automate test data generation, but it provides an interface to support it. Also like other Quest products, QCTO is licensed although there is a freeware copy.

Steven (disclosure, he he is one of my Oracle heroes) has written a feature comparison of all the PL/SQL testing tools. Obviously, QOTC comes out tops, but I think the comparison is honest. Check it out.

Advice on test fixtures in utplsql

Managing test data for unit testing can be a real pain in the neck. Unfortunately utplsql doesn't offer much to shoulder the burden. So

• Always test against known values:
  
  
  • Avoid using dbms_random;
  • Try to restrict the use of sequences to columns whose values don't matter;
  • Dates are also tricky. Avoid hard-coding dates: use variables which are populated with sysdate. Learn to appreciate add_months(), last_day(), interval, trunc(sysdate, 'MM'), etc.
• Isolate the test data from other users. Build it from scratch. Use distinctive values wherever possible.
• Only create as much test data as you need. Volumetric testing is a different responsibility.
• When testing procedures which change the data create specific records for each unit test.
• Also: don't rely on the successful output from one test to provide the input from another test.
• When testing procedures which simply report against data share records between unit tests when appropriate.
• Share framework data (e.g. referenced primary keys) whenever possible.
• Use free text fields (names, descriptions, comments) to identify which test or tests use the record.
• Minimise the work involved in creating new records:
  
  
  • Only assign values which are necessary to the test suite and the table's constraints;
  • Use default values as much as possible;
  • Proceduralize as much as possible.

Other things to bear in mind:

• setting up a test fixture can be a time-consuming exercise. If you have a lot of data consider building a procedure to set up the static data which can be run once per session, and include only volatile data in the ut_setup itself. This is especially helpful when testing read-only functionality.
• remember that creating test data is a programming exercise in its own right, and so prone to bugs.
• use all the features of utplsql. utAssert.EqQuery, utAssert.EqQueryValue, utAssert.EqTable, utAssert.EqTabCount and utAssert.Eq_RefC_Query are all very useful features when it comes to inferring the values of volatile data.
• when diagnosing a test run which didn't go the way we were expecting it can be useful to have the data which was used. So consider having a hollow ut_teardown procedure and clearing down the test data at the start of ut_setup.

Dealing with legacy code

Commenting on Gary's post reminded me of one other thing you may find useful. Steven F wrote ulplsql as a PL/SQL implementation of JUnit, the Java vanguard of the Test First movement. However, the techniques of TDD can be also applied to large amounts of legacy code (in this context, legacy code is any set of programs without any unit tests).

The key thing to bear in mind is that you don't have to get everything under unit test immediately. Start incrementally. Build unit tests for new stuff, Test First. Build unit tests for the bits you're going to change before you apply the change, so you know they still work after you have made the change.

There is a lot of thought in this area, but (inevitably if shamefully) it mainly comes from the OO programmers. Michael Feathers is the main chap. Read his article Working Effectively With Legacy Code. If you find it helpful he subsequently wrote a book of the same name.

How do I create automated tests for an Oracle procedure?

You could use one of the already mentioned PL/SQL targeted testing frameworks - which is probably the easiest solution to your problem.

If you really want to roll your own solution, PL/SQL procs/functions are easily callable from .NET, so you could just use xUnit as a runner:

• The key really is to make sure you are able to setup a specific known input scenario for each test. You can roll your own approach to this solution (sql scripts, custom configuration files, create the data in code), or use an existing framework targeted at resetting your database to an initial known state specific for each test (something like NDbUnit).

• Write a small wrapper library that allows you to call your procedures easily to make tests more readable and concise.

• To make sure your tests do not leave traces behind and you get caught up in test dependencies and ordering problems, you can use transactions as part of your tests - start a transaction in the [SetUp] method, rollback on [TearDown].

Validating a PL/SQL Stored Procedure OUT REF CURSOR in SQL Developer's Unit Testing framework

A Process Validation can be used to unit test datatypes that SQL Developer cannot handle.

1. Create an implementation and link (synchronize) it with a dummy procedure or function.
2. Set up parameters and results so that the Test completes with a Success
3. Create a Process Validation with 'User Pl/Sql Code' using the template shown

Template:

DECLARE
  l_Cursor  SYS_REFCURSOR ;
BEGIN
  OPEN l_Cursor FOR '<SQL goes here>' ;
  MY_PROCEDURE( l_Cursor ) ;
  <do validation>
  CLOSE l_Cursor;
  IF <not valid> THEN
    RAISE PROGRAM_ERROR ;
  END IF ;
END ;

Unit tests framework for databases

There is TSQLUnit... Link here: http://tsqlunit.sourceforge.net/

---

Related Topics