There are several unit testing frameworks available for unit testing Oracle PL/SQL code. Some of the populate unit testing frameworks are utPLSQL, ruby-plsql-spec and PL/Unit. Personally I would recommend using ruby-plsql-spec as it takes the Behavior Driven Development (BDD) unit testing approach. It also provides test execution and coverage reporting, which is really very useful.
- Install Ruby or JRuby if you come from a Java background.
- Install ruby-plsql-spec gem; Gems is a package manager for Ruby.
- In your project directory, setup the test directory. This will create sub-folder named spec in which all supporting files will be generated. Database details need to be configured in database.yml
- All unit tests should be created in the spec directory and name should end in ‘_spec.rb’.
gem install ruby-plsql-spec
- The following PLSQL function is to be tested, it parses a string and returns a number if it’s valid, else it returns null.
- Create a unit test parsenum_spec.rb as follows:
CREATE OR REPLACE FUNCTION PARSENUM( input IN VARCHAR2 )
RETURN NUMBER IS
WHEN OTHERS THEN
describe “parsenum test” do
it “should not get null for valid number” do
plsql.parsenum(‘100’).should_not == NULL
- To run the test, execute the following:
- On successful execution the following message will be displayed.
- To generate coverage reports, use the following command:
Running all specs from spec/ . Finished in 0.033782 seconds 1 example, 0 failures
plsql-spec run --coverage
You can see more examples here: