Friday, 13 November 2009

Database Unit Testing:- Do you do it?

Testing. When you're writing code, how often do you test it? Ok, you test it, but how often do you spend the time to put a set of discrete tests together to make sure that your code is doing what you thought it would do and moreover, it doesnt do what its not supposed to.

As part of SQL Developer 2.1, Unit Testing is one of the main features we have added. This is very different to anything you've seen in this area before. There are 5 different things on the new navigator we have added.

  1. Tests: These are individual or discreate tests against a piece of code like a stored procedure or a function or package. Each test can have several implementations of the test. I.e., if it is a stored procedure, you can have an implementation with n implementation depending on the permutations of the paramters that you want to support.
  2. Suites: This is a regular concept, like JUnit, which you can use to group your tests and run them together, in sequence. In fact, with 2.1, you can run unit tests from the command line which means you can hook them up with your build machine and run these in as part of a standard build and publish results with your builds
  3. reports: We have canned several reports so you can see how your suite of tests ran, what the coverage numbers are and allow you to drill through each implementation that was run.
  4. Lookups: Lookups are setup to allow you to predefine static parameter values that can be group by functional need and used in test implementations.
  5. Libraries: Like the word says, these are libraries of startups, teardowns, validations, and defined dynamic values that we want to use regularly in tests.

The test here shows each implementation of the test. The implementation is a set of parameters which will give you a specific result, based on processing the parameters as part of the stored procedure.

This is our first release of Unit Testing and we will add more features in the next few releases. This release allows you to set up suites of tests with parameters covered by all the scalar datatypes. We will cover more abstract types in future releases.

This is available as part of SQL Developer 2.1, which at time of writing this is available as an EA from Download it and play with it. If there are things you like, let us know and if there are things you would like added, then you can let us know on the feature request page as usual which is


rjlyders said...

I like the idea of the unit testing built into SQL Developer and and working towards using it. With the goal of validating constraints, unique and otherwise, it would be a great help to be able to simply enter the exact values for set of records via a grid tool that should be OK, and another set of records that should fail the constraint. Currently it appears that the only way to do this involves creating various custom unit testing procedures that test each aspect of a contraint... the current unit testing features don't seem to provide much help for unit testing contraints in a way that avoids a lot of custom coding for the insertion and deletion of records groups.

Sridevi Koduru said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.