October 05, 2010
Audience was quite interested and had a lot of questions and therefore it motivated me to do some more improvements to ruby-plsql-spec to make it easier for newcomers.
ruby-plsql-spec gem and plsql-spec command line utility
Initially ruby-plsql-spec was just repository of sample tests and if you wanted to start to use it in your project you had to manually pick necessary files and copy them to your project directory.
Now ruby-plsql-spec is released as a gem which includes all necessary dependencies (except ruby-oci8 which you should install if using MRI Ruby implementation) and you can install it with
gem install ruby-plsql-spec
When you have installed ruby-plsql-spec gem and want to start to use it in your existing project then go to your project directory and from command line execute
It will create
spec subdirectory in current directory where all initial supporting files will be created. The main configuration file which should be updated is
spec/database.yml where you should specify username, password and database connection string that should be used when running tests:
default: username: hr password: hr database: orcl
If you specify just
database: name then it will be used as TNS connection string (and TNS_ADMIN environment variable should point to directory where tnsnames.ora file is located) or you can also provide
hostname: and if necessary also
port: parameters and then you can connect to database without tnsnames.ora file.
Now you can start to create your tests in
spec directory and your tests file names should end with
_spec.rb. You can see some examples at ruby-plsql-spec examples directory
To validate your installation you can try to create simple dummy test in
require "spec_helper" describe "test installation" do it "should get SYSDATE" do plsql.sysdate.should_not == NULL end end
And now from command line you can try to run your test with:
If everything is fine you should see something similar like this:
Running all specs from spec/ . Finished in 0.033782 seconds 1 example, 0 failures
Code coverage reporting
During my Oracle OpenWorld presentation I also showed how to get PL/SQL code coverage report (which shows which PL/SQL code lines were executed during tests run). It might be useful when you want to identify which existing PL/SQL code is not yet covered by unit tests.
Now code coverage reporting is even easier with new ruby-plsql-spec gem. It uses Oracle database DBMS_PROFILER package to collect code coverage information and I took rcov reports HTML and CSS files to present results (so that they would be very similar to Ruby code coverage reports).
To try code coverage reporting let’s create simple PL/SQL function:
CREATE OR REPLACE FUNCTION test_profiler RETURN VARCHAR2 IS BEGIN RETURN 'test_profiler'; EXCEPTION WHEN OTHERS THEN RETURN 'others'; END;
and simple test to verify code coverage reporting:
require "spec_helper" describe "test code coverage" do it "should get result" do plsql.test_profiler.should == 'test_profiler' end end
And now you can run tests with
--coverage option which will produce code coverage report:
plsql-spec run --coverage
As a result code coverage reports are created in
coverage/ subdirectory. Open
coverage/index.html in your browser and click on
TEST_PROFILER function and you should see something similar like this report:
You can see that
RETURN 'test_profiler'; line (with green background) was executed by test but
RETURN 'others'; line (with red background) was not. Lines with light background are ignored by DBMS_PROFILER and I do not take them into account when calculating code coverage percentage (but they are taken into account when calculating total coverage percentage).
Questions or feedback
If you have any other questions about using ruby-plsql-spec for PL/SQL unit testing then please post comments here or if you find any issues when using ruby-plsql-spec then please report them at GitHub issues page.