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
BETWNSTR- example from utPLSQL project
AWARD_BONUS- example from SQL Developer 2.1 tutorial
REMOVE_ROOMS_BY_NAME- example from Quest Code Tester for Oracle tutorial
See Installing on Windows in separate file.
- Install Ruby 1.8.7 or Ruby 1.9.2
- Install Oracle client, e.g. Oracle Instant Client
Install ruby-oci8 and ruby-plsql-spec (prefix with sudo if necessary)
gem install ruby-oci8 gem install ruby-plsql-spec
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
which will create
spec directory where test files will be located.
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
helpers subdirectories per each module / functionality group.
All tests can be run from command line using
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
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
- Create new factory methods for test data creation in
factoriesdirectory (see example in