Introduction

Unit testing your PL/SQL from inside Oracle doesn't have to be difficult or expensive. There is no need to be tied into some third-party closed source tool and no reason to start from scratch writing your own framework. PLUTO can help get you started on the road to unit testing bliss, or at least unit testing.

Installing PLUTO

Installation couldn't be much easier, just download the latest version of the PLUTO source to a machine that can connect to your database, unpack the source file, and run:

  $> sqlplus @install.sql

It is recommended that you run the command in a schema called PLUTO and that the PLUTO schema have 'create type' privileges.

If you have any issues with the install, please be sure to log them. Be sure to mention the version of Oracle that you are running. And better yet, if you figured out how to fix the issue, please send in the patch.

What is installed?

PLUTO is intended to be a lightweight system for unit testing. It doesn't create any tables or store a lot of data. Instead, it creates a TYPE and a handful of objects.

Getting Started Unit Testing With PLUTO

After installing PLUTO, you are ready to start writing unit tests. You might be interested in some test examples. The text below highlights what you'll see in some of that example code.

To get started using PLUTO, you just have to subclass the main PLUTO object, pluto_obj. To do this in Oracle, you'll need to create a package specification.

  create or replace type testing_obj under pluto_obj(
  )
  instantiable not final;

Of course, this specification doesn't really accomplish much other than to create a child class of pluto_obj. We won't realize how powerful that is until a little later in this tutorial.

Adding some meat to the object, let's include a specification for a constructor.

  create or replace type testing_obj under pluto_obj(
    constructor function testing_obj
      return self as result
  )
  instantiable not final;

The implementation of the constructor simply creates a utility object and an output object. The utility object is stashed away in m_util_object. m_util_object is an attribute of pluto_obj that is intended to store the utility object. Typically, this part of the test class construction will be very similar to what you see below, with the exception being the type of output object constructed and passed to the utility object constructor.

The type of output object chosen at this point of your testing is very important. It determines what type of data will be emitted from the testing suite at runtime, be it the default human-readable data, TAP, inserts into a logging table, or some combination of all of the above. In the case below, we settle on using the default human-readable form of output object.

  create or replace type body testing_obj is
    constructor function testing_obj
      return self as result is
    begin
      m_util_object              :=
                     pluto_util_obj( output_object => pluto_output_obj( ));
      return;
    end testing_obj;
  end;

Excellent, we now have the minimum amount of code necessary to run unit tests through PLUTO, but we have a problem... have you spotted it?

We don't have any tests!

Let's add a test to our new testing object.

  create or replace type testing_obj under pluto_obj(
    member procedure test_one,
    constructor function testing_obj
      return self as result
  )
  instantiable not final;

  create or replace type body testing_obj is
    member procedure test_one is
    begin
      m_util_object.ok(
        test_passed    => true,
        test_label     => 'running test_one'      ||
                          chr( 10 )               ||
                          'i hope that it worked'
      );
    end test_one;
    constructor function testing_obj
      return self as result is
    begin
      m_util_object              :=
                     pluto_util_obj( output_object => pluto_output_obj( ));
      return;
    end testing_obj;
  end;

Okay, so it's not the most exciting test, but it works. You can see in the first part of the listing that we add a procedure specification called test_one. Later, we define that procedure in the type body. In the definition we invoke the ok method from the utility object, passing it a boolean value for the status of the test and a test label.

In order to run the test, we simply run an anonymous PL/SQL block.

  declare
    ut_obj  my_test_obj := my_test_obj( );
  begin
    ut_obj.run_tests;
  end;

This executes the test_one test and prints the results to the standard Oracle output stream. Of course, there is a lot more to PLUTO than simply running test methods one after another. PLUTO implements xUnit-style testing, which we'll go over shortly. Also, there is a host of functions and procedures available in the PLUTO utility module.

Order of Execution Within PLUTO

The basic idea of the PLUTO framework is to extend the pluto_obj object, create procedures that implement tests, and call run_tests to execute those tests. Of course, there is a little more to it than that. Not every procedure is a test procedure and not all test procedures are created equally.

PLUTO introspects a given testing object and looks for procedures that are called out as tests. Procedures are considered tests if they begin with the letters TEST. PLUTO finds all procedures prefixed with TEST and executes them in the order that they sort (ascending) according to the language settings of the current Oracle session.

Sometimes you need to do a little work before you get started testing though. Often there is a fixture that needs to execute that performs an expensive operation such as creating data structures or seeding data for the tests that are about to be performed. There is a good chance that you only really want to do most of these operations once per test run. To accomplish this, PLUTO looks for procedures that begin with the word STARTUP. Procedures prefixed with STARTUP execute before any TEST procedures execute. If there are multiple STARTUP procedures, they are executed in ascending order according to the language settings of the current Oracle session.

Of course, if you are creating objects, seeding data, or doing whatever wild and crazy test preparation at startup, you'll want to clean things up after you are finished testing. In many cases, you could probably wrap your tests in a transaction an simply rollback after the tests are complete. However, you can also take advantage of SHUTDOWN methods in PLUTO. These methods begin with the word SHUTDOWN and are executed after all TEST procedures are complete. You probably guessed it by now... if there are multiple SHUTDOWN procedures, they are executed in ascending order according to the language settings of the current Oracle session.

Great! We can now run our tests and wrap all of the tests in some startup and shutdown logic that makes sure that we are testing in a sane/standard/regular/regulated environment. But what if we need to do some sort of setup before each tests runs? Maybe we need to clean up some data that the test before might have changed or possibly reset some counter or buffer. SETUP procedures are there for you.

Any procedure that starts with the word SETUP is executed before each TEST procedure that is executed. This execution happens after any STARTUP procedures run. If there are multiple SETUP procedures, they are executed in ascending order according to the language settings of the current Oracle session.

Of course, SETUP wouldn't be complete without TEARDOWN. TEARDOWN procedures start with the word TEARDOWN and are executed after each TEST procedure. If there are multiple TEARDOWN procedures, they are executed in ascending order according to the language settings of the current Oracle session.

Any procedure that doesn't start with the word STARTUP, SETUP, TEST, TEARDOWN, or SHUTDOWN is ignored by PLUTO.

If you are having issues remembering the order or figuring out how often each procedure runs just remember that each STARTUP procedure runs once and runs before anything else. Similarly, each SHUTDOWN procedure runs once and runs after everything else. Each TEST method runs once and all of the test methods are sandwiched in between the STARTUP and SHUTDOWN procedure(s). The SETUP and TEARDOWN methods are the ones that get the most action typically. Each procedure is ran one time for each TEST procedure. SETUP runs before each test and TEARDOWN afterward.

To illustrate, here is an example of the order of execution of a set of imaginary procedures in a PLUTO object:

• STARTUP_SYSTEM
• SETUP_FOR_TESTING
• TEST_ONE
• TEARDOWN_AFTER_TESTING
• SETUP_FOR_TESTING
• TEST_TWO
• TEARDOWN_AFTER_TESTING
• SHUTDOWN_SYSTEM