PL/SQL unit testing with Ruby

Unit testing of PL/SQL procedures with Ruby libraries:

  • ruby-plsql - Ruby API for calling PL/SQL procedures
  • RSpec - Ruby testing (or behavior driven development) framework


PL/SQL procedure examples are in examples/source subdirectory, test examples are in examples/spec subdirectory.


See Installing on Windows in separate file.

Another alternative is to use JRuby if for example it is necessary also to test Java classes / methods using Ruby.

  • Install JRuby
  • Copy Oracle JDBC driver (e.g. ojdbc14.jar) to JRUBY_HOME/lib directory
  • Install ruby-plsql-spec (prefix with sudo if necessary)

    jruby -S gem install ruby-plsql-spec

Initializing project directory

In your project directory execute

    plsql-spec init

which will create spec directory where test files will be located.

Modify spec/database.yml file and specify database connection which should be used when running tests. In database: parameter specify either TNS connection name or use "servername/databasename" or "servername:port/databasename" to specify host, port and database name.

Start creating tests in files with _spec.rb at the end of file name. If there will be not so many files then you can place them directly in spec directory. If there will be many tests files then create separate directories per module / functionality group and place tests files in subdirectories. You can also create factories and helpers subdirectories per each module / functionality group.

Executing tests

All tests can be run from command line using

    plsql-spec run

or if you want to run tests just from one file then use, e.g.

    plsql-spec run spec/example_spec.rb

You can get additional help about plsql-spec command line utility with

    plsql-spec help

Code coverage reporting

If you would like to see PL/SQL code coverage report (which lines of code were executed during tests run) then run tests with --coverage option:

    plsql-spec run --coverage

Coverage reports will be created as HTML files in coverage/ directory. Open with your browser coverage/index.html file.

Code coverage is gathered using DBMS_PROFILER package. Please take into account that only those packages will be analyzed to which current database session user has CREATE privilege.

How to start?

Read blog post about Oracle PL/SQL unit testing with Ruby.

If you are not familiar with Ruby 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.

How to customize ruby-plsql-spec for my project?

  • Review spec/spec_helper.rb file and modify if needed directories where you will store additional required files (helper files, factory files, source files).
  • Review and or create new helper files in spec\helpers directory.
  • Create new factory methods for test data creation in factories directory (see example in examples/spec/factories).