A collection of tutorials, code and tools to help you get better using Oracle
03 October 2012
I do a lot of programming with Ruby, and a lot of Oracle PLSQL development, and one thing that is hard to miss between the two communities is the attitude toward unit testing.
In Ruby TDD is well adopted and there are many mature tools available to make testing easy. In the Oracle space, unit testing doesn't seem to be so common (at least where I have worked) and the testing tools available are not so great.
Aside from the tools, testing database code is also a little more cumbersome that testing well designed OO code. When teams do TDD, they tend to mock the DB interactions out, allowing tests to run without a DB present, simplifying the testing stack. Unfortunately, when testing PLSQL not only does the database need to be there, but it is essential as it is the thing under test!
If you want to write some automated tests for PLSQL, so far as I can tell you have three options.
utPLSQL is a great piece of work and it takes what can be done with PLSQL to the limit. For me the problem is that it's too verbose. Or more correctly I should say that PLSQL is too verbose. The need for both a package spec and body, the fact it is statically typed and needs to be complied after each change makes it cumbersome to work with. This certainly doesn't make testing get out of your way, and I often found myself just giving up on it.
I have used it on a couple of standalone packages I developed, but I just couldn't see me using it in a major project, or convincing a team of developers to adopt it. There was just too much repetitive code to write.
In a nut shell, utPLSQL is quite like JUnit or Test::Unit in Ruby or Perl. It encourages one test file per code unit being tested, has a pre-test setup and post-test teardown step and then tests can be written that assert certain conditions meet the test criteria. An example straight from the utPLSQL documentation demonstrates this:
/*file ut_str.pkb */
CREATE OR REPLACE PACKAGE BODY ut_str
IS
PROCEDURE ut_setup
IS
BEGIN
NULL;
END;
PROCEDURE ut_teardown
IS
BEGIN
NULL;
END;
-- For each program to test...
PROCEDURE ut_betwn IS
BEGIN
utAssert.eq (
'Typical Valid Usage',
str.betwn ('this is a string', 3, 7),
'is is'
);
utAssert.eq (
'Test Negative Start',
str.betwn ('this is a string', -3, 7),
'ing'
);
utAssert.isNULL (
'Start bigger than end',
str.betwn ('this is a string', 3, 1)
);
END ut_betwn;
END ut_str;
/
Both Oracle and Quest have come up with alternatives to utPLSQL, and encourage a GUI driven approach to unit testing. First off, I will admit I have never used these tools, but at the same time, I cannot see myself defining tests in a GUI.
These days part of any unit testing strategy should also give thought to automated CI, where the tests can be run automatically as changes are committed to version control. These GUI tools can make this difficult, but not impossible.
The GUI tools also tend to use a repository inside the database to store tests. I don't like this - I'd prefer my tests to be in files, inside version control where I can easily check them in and out and clearly see how they work. Maybe I am just an old fashioned command line guy, but I don't tend to do anything using a GUI if at all possible.
Sometimes unit tests can be quite repetitive (varying inputs over many calls to the same procedure), and using a programming language much of this repetition can be factored away. With a GUI I am not so sure.
The final reason I am against GUI testing tools, is you often find a requirement that would be very simple in a programming language, but getting the GUI to do what you need is impossible or very difficult.
As I have quite a lot of experience coding in Ruby, in my latest project I decided to experiment with using Ruby to unit test PLSQL code. Ruby has lots of testing libraries available, it has the ability to invoke PLSQL procedures and query the database, so it is certainly possible.
The first stumbling block I hit, was that the OCI8 driver for Ruby didn't support some of the features I needed to test (as least in the version I used when I started). I got around this by using JRuby which provides access to the Oracle JDBC classes. The nice thing about using the Oracle JDBC classes to connect to the database, is that they support just about every Oracle feature available.
After deciding on JRuby with JDBC, the next problem was that raw JDBC is too verbose to write concise tests. There is too much to worry about with binding variables, datatypes, result sets etc, and my code was too messy and not very Ruby like.
None of these problems were show stoppers, and a few 100 lines of Ruby code later I created the SimpleOracleJDBC gem, providing a much cleaner interface for invoking a stored procedure or SQL statement and getting the results. For instance the following code would invoke the procedure my_proc, passing the value "String Input" as the input parameter:
proc = dbinterface.prepare_proc("begin :result := my_proc(:input); end;")
proc.execute([Int, nil, :out], "String Input")
# check the procedure returns zero
assert_equal(proc[1], 0)
With a clean and concise way to call stored procedures, unit testing in PLSQL with Ruby can proceed in the same way as unit testing Ruby with Ruby. You get clean, compact, dynamically typed Ruby code that gets out of your way when writing tests. It is easily added to version control and testing is easily automated.
One major problem with using Ruby to unit test PLSQL is that the number of Oracle developers with good enough Ruby knowledge is probably somewhat limited. This could be a problem when trying to convince your team to use this approach. That said an in depth knowledge of Ruby is not required to write simple tests, so it shouldn't take too long for experienced software engineers to get up to speed.
In my next post, I will explore in more detail how to bring my SimpleOracleJDBC gem together with my DataFactory gem to unit test a series of PLSQL objects.