November 27, 2009

Oracle PL/SQL unit testing with Ruby

Filed under: oracle,pl/sql,ruby — Raimonds Simanovskis @ 2:18 pm

Current PL/SQL unit testing options

Unit testing and TDD (test driven development) practices are nowadays one of the key software development practices. It is especially important if you are doing agile software development in small iterations where you need to automate unit testing as much as possible, as you cannot do manual regression testing of all existing and new functionality at the end of each iteration.

In some languages (like Java, Ruby, Python, C# etc.) there is quite good tools and frameworks support for unit testing and as a result there is quite high testing culture among top developers in these communities. But unfortunately in PL/SQL community so far automated unit testing is not used very often. During recent Oracle OpenWorld conference in presentations about unit testing when it was asked who is doing automated unit testing then only few hands were raised.

Why is it so? And what are current options for doing automated PL/SQL unit testing?

The first unit testing framework for PL/SQL was utPLSQL which was created by Steven Feuerstein and based on API defined by many other xUnit style frameworks (like e.g. JUnit). But the issue with this approach was that PL/SQL syntax for tests was quite verbose and tests were not very readable (see example). As a result Steven stopped developing further utPLSQL and currently there are no other active maintainers of this project. There are some other alternative frameworks which tried to simplify writing tests in PL/SQL (OUnit, pl/unit, PLUTO etc.) but none of them are very actively used and maintained by PL/SQL community.

Because of the issues with utPLSQL Steven Feuerstein started development of graphical interface tool for PL/SQL unit testing which is now Quest Code Tester for Oracle. This tool is actively developed and maintained by Quest Software but there are several issues with it:

  • It is a commercial tool and as a result it will not become widely accepted by all PL/SQL developers. There is also a freeware edition of it but the functionality of it is very limited.
  • It is a graphical tool – it can help you with quick creation of simple tests but when you will need more complex logic you might get stuck that you cannot do it (or you need to do it again in plain PL/SQL and have the same issues as in utPLSQL).
  • It stores tests in database repository – and it means that it might be hard to maintain unit tests in version control system like Subversion or Git.

And finally also Oracle started to do something in PL/SQL unit testing area and there is unit testing support in latest SQL Developer version 2.1 which currently still is in early adopter status. SQL Developer has very similar approach to Quest Code Tester – it is graphical tool which stores tests and test results in repository. So the benefit of SQL Developer over Quest Code Tester is that it is free :) But compared to Quest Code Tester it still has less features (e.g. currently not all complex data types are supported) and still is not released as final version and still has bugs.

Ruby as testing tool for PL/SQL

As you probably know I am quite big Ruby fan and always exploring new ways how to use Ruby to increase my productivity. And Ruby community has very high testing culture and has many good tools for testing support (I like and use RSpec testing framework). Therefore some time ago I started to use Ruby and RSpec also for testing PL/SQL code in our projects where we use Ruby on Rails on top of Oracle databases with existing PL/SQL business logic.

I have created ruby-plsql library which provides very easy API for calling PL/SQL procedures from Ruby and recent ruby-plsql version supports majority of PL/SQL data types.

So let’s start with with simple example how to use Ruby, RSpec and ruby-plsql to create PL/SQL procedure unit test. I will use BETWNSTR procedure example from utPLSQL examples:

   string_in   IN   VARCHAR2,
   start_in    IN   INTEGER,
   end_in      IN   INTEGER
   l_start PLS_INTEGER := start_in;
   IF l_start = 0
      l_start := 1;
   END IF;
   RETURN (SUBSTR (string_in, l_start, end_in - l_start + 1));

I took example tests from utPLSQL and wrote them in Ruby and RSpec:

describe "Between string" do
  it "should be correct in normal case" do
    plsql.betwnstr('abcdefg', 2, 5).should == 'bcde'
  it "should be correct with zero start value" do
    plsql.betwnstr('abcdefg', 0, 5).should == 'abcde'
  it "should be correct with way big end value" do
    plsql.betwnstr('abcdefg', 5, 500).should == 'efg'
  it "should be correct with NULL string" do
    plsql.betwnstr(nil, 5, 500).should be_nil

As you can see the tests are much shorter than in utPLSQL and are much more readable (also more readable than utPLSQL template which can be used to generate utPLSQL tests). And also you can create these tests faster than using GUI tools like Quest Code Tester or SQL Developer.

More complex example

Second more complex example I took from SQL Developer unit testing tutorial. We will create tests for PL/SQL procedure AWARD_BONUS:

 PROCEDURE award_bonus (
  emp_id NUMBER, sales_amt NUMBER) AS
  commission    REAL;
  comm_missing  EXCEPTION;
  SELECT commission_pct INTO commission
    FROM employees2
      WHERE employee_id = emp_id;
  IF commission IS NULL THEN
    RAISE comm_missing;
    UPDATE employees2
      SET salary = NVL(salary,0) + sales_amt*commission
        WHERE employee_id = emp_id;
END award_bonus;

I didn’t quite like the testing approach in SQL Developer unit testing tutorial – it was assuming that there is already specific data in employees2 table and was testing procedure using specific primary key values. As a result tests are not very readable as you cannot see all input data in the test case and tests could easily broke if initial data in table are different.

Therefore I created tests in Ruby using better approach that each test creates all necessary data that are needed for it and at the end of test there are no side effects which can influence other tests:

describe "Award bonus" do
  include CustomerFactory
  [ [1000,  1234.55,  0.10,   1123.46],
    [nil,   1234.56,  0.10,   123.46],
    [1000,  1234.54,  0.10,   1123.45]
  ].each do |salary, sales_amt, commission_pct, result|
    it "should calculate base salary #{salary.inspect} + sales amount #{sales_amt} * commission percentage #{commission_pct} = salary #{result.inspect}" do
      employee = create_employee(
        :commission_pct => commission_pct,
        :salary => salary
      plsql.award_bonus(employee[:employee_id], sales_amt)
      get_employee(employee[:employee_id])[:salary].should == result

I am generating three different tests with three different sets of input values. When you run these tests you see result:

Award bonus
- should calculate base salary 1000 + sales amount 1234.55 * commission percentage 0.1 = salary 1123.46
- should calculate base salary NULL + sales amount 1234.56 * commission percentage 0.1 = salary 123.46
- should calculate base salary 1000 + sales amount 1234.54 * commission percentage 0.1 = salary 1123.45

In addition I am using factory pattern (create_customer method) for test data creation. When using factory pattern you create test data creation method which will create valid new record with default field values. If in your test you need some specific non-default values then you can pass just these values as parameters to factory method. Factory pattern also helps in the maintenance of tests. For example, if new mandatory columns will be added to employees table then it will be necessary to add new fields with default values in factory methods and nothing should be changed in individual tests.

Here is example of employee factory implementation:

module EmployeeFactory
  # Creates new employee with valid field values.
  # Pass in parameters only field values that you want to override.
  def create_employee(params)
    employee = {
      :employee_id => plsql.employees2_seq.nextval,
      :last_name => 'Last',
      :email => 'last@example.com',
      :hire_date => Date.today,
      :job_id => plsql.jobs.first[:job_id],
      :commission_pct => nil,
      :salary => nil
    plsql.employees2.insert employee
    get_employee employee[:employee_id]
  # Select employee by primary key
  def get_employee(employee_id)
    plsql.employees2.first :employee_id => employee_id

And here is additional test for testing if procedure will raise exception if one input value is missing:

  it "should raise ORA-06510 exception if commission percentage is missing" do
    salary, sales_amt, commission_pct = 1000,  1234.55,  nil
    employee = create_employee(
      :commission_pct => commission_pct,
      :salary => salary
    lambda do
      plsql.award_bonus(employee[:employee_id], sales_amt)
    end.should raise_error(/ORA-06510/)

How to use it

I hope that if you are looking for PL/SQL unit testing tool then you will try this out :) You can get examples from this article together with necessary setup code and installation instructions at http://github.com/rsim/ruby-plsql-spec.

If you have any feedback or questions or feature suggestions then please comment.



  1. In my experience the following is true:

    1) trivial code == trivial unit tests
    2) non-trivial code == non-trivial unit tests

    Because of this one has to take different approaches to readability for different scales of testing.
    Want to test 3 line procedure that changes one field in a table with no triggers? Sure, you can easily generate tests using whatever methodology you like. But you shoud try to create tests that use data from some 20 different tables that totals to 400+ fields and 100+ total records that all can influence outcome and also include transaction management. Oh, and that outcome is not a single return value, but some possible inserts/updates in any number of other tables. That is actually too much data to *see together* for any test. So how should people manage it? Is it really that much better to store it in plaintext than it would be in database? Are CSV files better than INSERT INTO scripts? I think it is *data* that influences how things behave in *database*, so I say that it should reside in that same *database*. (You can store data generation scripts in version control, sure.) Anyway, does it really matter, how you generate your data on this scale? I’ve yet to see how can it not be ugly one way or another.

    TL;DR version:
    this does not address my problems with automated pl/sql testing, just ads another layer of abstraction. And we all know what Joel said about abstractions :(

    Comment by jva — November 27, 2009 @ 4:31 pm

  2. Second thought:

    Maybe I’m not the target audience for this tool. I’m a PL/SQL developer, writing almost exclusively PL/SQL code. Introducing lots of Ruby code would be huge overhead all by itself. Your approach would probably be usefull to people that only occasionaly step out of Ruby to write a bit of PL/SQL and already have a working knowledge of unit testing for Ruby.

    Comment by jva — November 27, 2009 @ 4:54 pm

  3. Non-trivial code should be split into smaller units which could be unit tested separately. It will be very hard to maintain one unit which updates 400+ fields. And if one program unit updates any number of other tables then probably it is also not a good design and is hard to maintain.

    Good unit test should include both input data and results and should leave test database in the state as it was before test. Therefore it is better to keep test data generation inside unit test and not somewhere else – it helps both for readability for test (you see all inputs in test) as well as maintainability (if related things are stored in different places then it is harder to notice what else should be changed).

    And I also recommend to separate unit tests (where you validate logic on specific small examples and test all different edge cases) from performance / integration tests where you test just some basic scenarios on large data volumes.

    And last comment about “PL/SQL developer”. If the only tool you have is hammer then all problems seem to be nails :) Good software developer should have several tools and apply the right tool for the right problem. If PL/SQL is not a good language for testing (which could be seen from failure of utPLSQL project) then good developers should look for better tools for the job. And dynamic languages like Ruby or Python are better for such task. And you shouldn’t be an expert Ruby programmer to use this approach – the amount of learning of described framework probably is similar to learning GUI testing frameworks like Quest Code Tester or SQL Developer unit testing. And I am eager to help with advice and suggestions how to learn this approach faster :)

    Comment by Raimonds Simanovskis — November 27, 2009 @ 6:28 pm

    • The criteria for “good” unit tests above resonates with me. I would like to look into unit testing using ruby-plsql. However, I have no background in Ruby programming. Could you please share some tips on how to adopt this approach quickly?

      Comment by tobias — December 11, 2009 @ 9:36 am

      • I recommend to start with Ruby in Twenty Minutes tutorial. Then you can take a look on some RSpec examples how to write and structure tests. And then you can take a look at ruby-plsql own tests to see how to pass parameters and verify results for different PL/SQL data types.

        I will put these links and some additional guidelines in ruby-plsql-spec README as it seems it will be valuable for many PL/SQL developers who would like to try this out.

        Comment by Raimonds Simanovskis — December 11, 2009 @ 3:27 pm

  4. These smaller units then should be treated as private procedures and should not be accessible from outside the package, so it will not be unit-testable anyway. Or do we trade one bad practice for another?

    Comment by jva — November 30, 2009 @ 12:23 pm

  5. Also, can we get an article on how to refactor existing PL/SQL code to unit-testable PL/SQL code without severe performance penalty (that means no slow-by-slow http://tkyte.blogspot.com/2006/10/slow-by-slow.html)?

    procedure process_customers_123 (bookkeeping_period in date)
    open cursor customers;
    fetch bulk collect into r_customers limit 1000;
    exit when no_data_found;
    forall i in 1..r_customers.count update customers set case when.., case when.., case when..;
    forall i in 1..r_customers.count insert into other_table1 values …;
    forall i in 1..r_customers.count update other_table2 set case when.., decode(), case when…;
    — and so on
    end loop;


    Comment by jva — November 30, 2009 @ 12:39 pm

  6. Cool stuff, I am using Oracle (and am a big fan of Ruby) and will definitely try this out. I have already one or two smaller but critical procedures in mind for this. Thanks for sharing this!

    Comment by Douwe Vonk — March 3, 2010 @ 10:11 pm

RSS feed for comments on this post.

Create a free website or blog at WordPress.com.

%d bloggers like this: