Unit Testing is around for quite a while. Since it started to become more and more popular, quite a few tools became available for Oracle database to allow unit testing of the database.
There are the UI based tools like Quest Code Tester (now Dell Code Tester for Oracle), Oracle SQL Developer unit testing. There is DBFit by Goyko Adzic for regression and functional testing of databases (including but not limited to Oracle), there are probably many more of that kind, that I’m not aware of.
There are also pure programming language based frameworks like UTPLSQL and ruby-plsql. There are probably (and hopefully) many more of that sort that I’m not aware of.

Unit tests are meant to be created and maintained by software developers and are to help developers keep their code clean and valid throughout entire project life cycle.
UI based frameworks for unit testing are putting a high abstraction and tight facade between the code and the developer. Those frameworks tend to limit the richness and variety of possible implementations for a test case and therefore are not suited for software developers. The time and cost of development and maintenance for UI-based unit tests is usually way beyond the benefits.
Variety of program units that are possible to develop is infinite and the only limitations to what the unit can do are the programming language boundaries and developers creativity. For this reason itself, it is best to use a programming language of similar or higher flexibility to describe and test the behaviour for a unit.
In this series of article I will focus on two programming language-based unit testing frameworks for Oralce, that I got familiar with and had opportunity to use.

History and numbers

UTPLSQL created by Steven Feuerstein in 2000 – yes 15 years ago!
It was maintained till 2005, then the project died for 8 years. It was revitalised in 2014. It had 13 releases up till 06.2015.
The total downloads of the project according to sourceforge stats is ~47 000
It seems to become quite popular among Oracle developers and therefore revitalized. I heard people in Poland knowing at least what it is. I’m aware of few quite large companies that actually use it as a tool of choice.

ruby-plsql created by Raimonds Simanovskis was first released in 2008, eight years after UTPLSQL.
Since then it had 15 releases till 06.2015.
It was downloaded over 68 000 times as a Ruby gem, according to rubygems.org

ruby-plsql-spec created by Raimonds Simanovskis is a neat utility library on top of ruby-plsql that aims to ease all the fuss around setup for RSpec unit testing easier. It also provides code-coverage reporting.
It had 4 releases and is currently in version 0.4.0.
It was downloaded over 5 000 times as a Ruby gem, according to rubygems.org

RSpec is an industry standard Ruby Unit Testing framework for Behaviour Driven Development (BDD).
It was first released in 07.2005 and since then had 159 versions published.
It was downloaded over 24 000 000 times according to rubygems.org


Before I dig into the details, I’d like to give a big thank you to Steven Feuerstein for creating UTPLSQL.
Creation of UTPLSQL was an enormous achievement. It was the first successful initiative to take Oracle database Unit Testing seriously and provide set of tools for writing unit tests for SQL and PLSQL code.

I’d like to thank Raimonds Simanovskis for taking the challenge to build a more flexible future-proofed and feature rich library for Oracle unit testing. Thanks to the library, within months, two database developers without any former knowledge of Ruby, RSpec and the library, managed to create over 1500 valuable unit tests for a complex, computation intensive, legacy database system. Tests were automatically pulled from version control system into continuous integration server and were actually delivering continuous feedback for the 6-person database development team. The tests became indicators of the quality and stability of the developed database and provided a safety harness for huge refactoring and performance improvements, that no one would dare to do without the tests giving certainty that all the functional requirements are still met.

A note on naming used in comparison

RSpec, ruby-plsql and ruby-plsql-spec along with some other Ruby libraries build a framework for Oracle unit testing within Ruby programming language. Those libraries provide different sets of functionalities that comprise the whole utility. In the below sections I will be comparing UTPLSQL to Ruby, RSpec, ruby-plsql or ruby-plsql-spec, depending on which features are compared.

The Conceptual differences between UTPLSQL / ruby-plsql

UTPLSQL is based on the Oracle database, SQL and PL/SQL procedural programming language.
RSpec, ruby-plsql and ruby-plsql-spec are libraries of Ruby, object oriented, dynamic language.

UTPLSQL library itself resides within the database that is to be tested.
ruby-plsql, ruby-plsql-spec and all the dependant libraries are part of Ruby gems within Ruby installation outside of database. Much like Oracle Client software, they live on the “client” side.

UTPLSQL unit tests must compiled into the database in the same schema/user as the tested program units.
RSpec tests reside outside of database and only contact the database to perform the testing operation.

UTPLSQL unit tests executed from within database that is to be tested.
RSpec unit tests are executed from the project source control directory.

UTPLSQL tests are executed within a context of a single session/database connection.
ruby-plsql manages database connections by using Oracle OCI8 library (Ruby/Rubinius) or Oracle JDBC driver (JRuby). Any test can refer to multiple users/connections/databases if needed.


The conceptual differences are fundamental.
ruby-plsql is not invasive. The tests live outside of the database and only contact the database to perform the testing operation, their existence has no direct impact on the database state.
UTPLSQL is invasive. The library itself as well as the tests live inside the database that it is testing. It affects the overall health of the database, when tests are invalidated. It needs to coexist with the product code. The very existence of unit tests and unit testing library on DEV database make it incompatible with all non-DEV databases. The databases are simply different. Importing non-DEV database to DEV database will wipe-out all the tests (and probably the testing library too).
ruby-plsql test can refer to multiple users/connections/databases within single test if needed. It manages database connections by using Oracle OCI8 library (Ruby/Rubinius) or Oracle JDBC driver (JRuby) and allows creation of cross-database / cross session / cross user tests. Unit test is on top of database connection.
UTPLSQL tests are executed within a context of a single session/database connection. The dependency is inverted and the test is executed within the context of a particular connection, so the framework does not allow such flexibility. Of course you can use some external tools to manage connections and run tests in different contexts but that is not part of the framework and still doesn’t allow a single tests to reference several connections.

In most of the cases, using UTPLSQL, you would connect to the database to execute all of your tests in the context of a user (the tester). This is what the framework supports and that seems to be simplest approach.
But what if you would like to test something across two users?

Example code and tests

We have a requirement to have a database package for sending a message between different database sessions.
The package could look like the one below.

  --Source from https://oracle-base.com/articles/misc/dbms_pipe
  PROCEDURE send_msg (
    p_number  IN  NUMBER,
    p_text    IN  VARCHAR2,
    p_date    IN  DATE DEFAULT SYSDATE);
  PROCEDURE receive_msg (
    p_number  OUT  NUMBER,
    p_text    OUT  VARCHAR2,
    p_date    OUT  DATE);
END message_api;

  --Source from https://oracle-base.com/articles/misc/dbms_pipe
  PROCEDURE send_msg (
    p_number  IN  NUMBER,
    p_text    IN  VARCHAR2,
  ) AS
    l_status  NUMBER;

      l_status := DBMS_PIPE.send_message('message_pipe');
      IF l_status != 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'message_pipe error');
      END IF;

  PROCEDURE receive_msg (
    p_number  OUT  NUMBER,
    p_text    OUT  VARCHAR2,
    p_date    OUT  DATE
  ) AS
    l_result  INTEGER;
      l_result := DBMS_PIPE.receive_message (
          pipename => 'message_pipe', timeout  => DBMS_PIPE.maxwait);
      IF l_result = 0 THEN
        -- Message received successfully.
           'message_api.receive was unsuccessful. Return result: ' || l_result);
      END IF;
END message_api;

With ruby-plsql you can create a set of tests to cover different scenarios.

require_relative '../spec_helper'

describe 'cross session communication package' do

  it 'allows receiving message sent in one session' do
    a_message = { p_number: 1, p_text: 'a message', p_date: Time.today }
    plsql.message_api.send_msg( a_message )
    expect( plsql.message_api.receive_msg ).to eq( a_message )

  it 'allows sending and receiving message across different sessions' do
    message_values = [ 1, 'a message', Time.today ]
    plsql(:default).message_api.send_msg( *message_values )
    received_values = plsql(:secondary).message_api.receive_msg.values
    expect( received_values ).to eq( message_values )

  it 'allows sending and receiving message across sessions of different users' do
    # a very PL/SQL'ish style of code
    a_number = 1
    a_text = 'a message'
    a_date = Time.today
    plsql.message_api.send_msg( a_number, a_text, a_date)

    result = plsql(:different_user).message_api.receive_msg

    expect( result[:p_number] ).to eq( a_number )
    expect( result[:p_text] ).to eq( a_text )
    expect( result[:p_date] ).to eq( a_date )


We can create an UTPLSQL package to test that the package is sending message.
The UTPLSQL will not allwo us however to test it across different sessions (the inverted dependency).
So the example below will represent only the first test case we have created in ruby-plsql.

  PROCEDURE ut_setup;
  PROCEDURE ut_teardown;

  PROCEDURE ut_send_receive_msg;
  PROCEDURE ut_setup IS

  PROCEDURE ut_teardown IS

  PROCEDURE ut_send_receive_msg IS
    l_num  NUMBER;
    l_txt  VARCHAR2(32767);
    l_date DATE;
    l_num_expected  NUMBER;
    l_txt_expected  VARCHAR2(32767);
    l_date_expected DATE;
      l_num_expected := 1;
      l_txt_expected := 'a message';
      l_date_expected := TRUNC( SYSDATE );

      message_api.send_msg( l_num_expected, l_txt_expected, l_date_expected );

      message_api.receive_msg( l_num, l_txt, l_date );

      utAssert.eq ( 'when I send a message and receive it then I get a valid message number', l_num, l_num_expected );
      utAssert.eq ( 'and I get a valid message date', l_date, l_date_expected );
      utAssert.eq ( 'and I get a valid message text', l_txt, l_txt_expected );


Looking at those two implementations of unit tests.
RSpec syntax is much closer to natural language. It is far more readable and dense.
It took 32 lines of code for 3 tests (one test was done over-verbose on purpose). There is very little noise in tests when you read them.
UTPLSQL required 41 lines of code to write one test. There is noise in the code and it really hard to put a meaningful text when calling utAssert.
The ruby-plsql unit tests are able to cover all the required test cases, as multi-session/multi-user tests are possible to implement.
UTPLSQL is not able to cover the required scenarios of multi-session/multi-user communication.

Ruby syntax is flexible enough so that you can start writing tests with syntax that is most fitted for you. It’s possible to write very verbose tests. It is also possible to write very compact tests, once you learn how to do it.

Coming up next:

to be continued…

3 comments on “UTPLSQL vs. ruby-plsql/ruby-plsql-spec – part one

  • well done article Jacek. Just a small suggestion, probably you should stress the purpose of automated unit tests and how it helps the quality. Additionally, I think you should compare also other parameters like a) ease of use b) complexity c) maintainability e) continuous integration aspects for comparison

    • Thank you Hasan for the valuable comment.
      I will summarize the series with opinions about ease of use and maintainability, but first I want to go through different use-case scenarios, to show how each tool is handling it. Otherwise, the post would be just an opinion without giving a in-depth explanation of the differences.
      This can be particularly important for all the people that see all the benefits of using a programming language based unit framework, but are struggling with limitations of UTPLSQL without seeing any good alternative.
      I plan to go through the continuous integration aspects as well. It just takes a lot of time to prepare valuable examples to make a good guide and comparison.

Leave a Reply