PLSQL Unit Testing with ruby-plsql-spec

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.

Installation:

  1. Install Ruby or JRuby if you come from a Java background. Smile
  2. Install ruby-plsql-spec gem; Gems is a package manager for Ruby.
  3. gem install ruby-plsql-spec
  4. 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
  5. plsql-spec init
  6. All unit tests should be created in the spec directory and name should end in ‘_spec.rb’.

Creating Tests:

  1. The following PLSQL function is to be tested, it parses a string and returns a number if it’s valid, else it returns null.
  2. CREATE OR REPLACE FUNCTION PARSENUM( input IN VARCHAR2 )

    RETURN NUMBER IS

    BEGIN

    RETURN to_number(input);

    EXCEPTION

    WHEN OTHERS THEN

    RETURN NULL;

    END;

  3. Create a unit test parsenum_spec.rb as follows:
  4. require “spec_helper”

     

    describe “parsenum test” do

    it “should not get null for valid number” do

    plsql.parsenum(‘100’).should_not == NULL

    end

    end

Running Tests:

  1. To run the test, execute the following:
  2. plsql-spec run
  3. On successful execution the following message will be displayed.
  4. Running all specs from spec/
    .
    
    Finished in 0.033782 seconds
    
    1 example, 0 failures
  5. To generate coverage reports, use the following command:
  6. plsql-spec run --coverage

You can see more examples here:
https://github.com/rsim/ruby-plsql-spec/examples/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s