OGB Appreciation Day: Explore a utPLSQL test scenario with “force-manual-rollback”

Most developers have – at some point – to deal with code they don’t know. Sometimes they don’t even have a clue what something is doing, but are expected to understand and probably change that something quickly. The situation gets worse if the functionality contains some side-effects or non obvious functionality, triggers for example in the database sphere.

I often encourage people to use unit tests to explore an unknown scenario or functionality, and utPLSQL provides a great tool to do that (since version 3.1.6): The a_force_manual_rollback-Parameter.

Consider the following tables and package (as always, you can find a full example incl. setup in my git repository):

create table deathstar_rooms (
  id integer generated by default on null as identity primary key,
  name varchar2(200) not null,
  code varchar2(200) not null unique
);

create table room_inventory (
  id integer generated by default on null as identity primary key,
  room_id integer not null,
  item varchar2(400) not null,
  nr_in_room integer,
  constraint room_inventory_fk_room foreign key ( room_id )
    references deathstar_rooms (id)
);

create or replace package room_util as
  subtype varchar2_nn is varchar2 not null;
  procedure add_item(
    i_item_name in varchar2_nn,
    i_room_code in varchar2_nn);
end;
/

We don’t care for the actual implementation, but we want to find out what’s going on when do add_item, secure that behaviour and then probably start refactoring or dig deeper into the code.

The usual way we would do this is to go ahead and just call that add_item function and then check what happened in the tables. But maybe we already have some code to setup a test room or we want to be able to write a unit test quickly after we increase our understanding of the functionality – so let’s start directly with writing a unit-test:

create or replace package ut_room_inventory as
  -- %suite(Room Inventory)

  -- %beforeall
  procedure setup_test_room;

  -- %test(Add a new item to the inventory of a room)
  procedure add_item;
end;
/

create or replace package body ut_room_inventory as
  /* Just add a test-room we can rely on */
  procedure setup_test_room
  as
    begin
      insert into deathstar_rooms ( id, name, code )
        values ( -1, 'Secret Test chamber', 'TEST');
    end;

  procedure add_item
  as
    begin
      -- Lets just add some things and evaluate what we
      -- should even test for
      room_util.add_item('Light saber (red)', 'TEST');
      room_util.add_item('Light saber (blue)', 'TEST');
      room_util.add_item('Light saber (green)', 'TEST');
    end;
end;
/

Great – we already have a test that does … nothing. Due to utPLSQL’s rollback mechanism, everything is rolled back when we run the test. We could of course run setup_test_room and add_item in a transaction, but imagine the overhead we’d need with a more complex setup with hierarchic suites, contexts etc.

begin
  ut.run(a_path=>'ut_room_inventory', a_force_manual_rollback=>true);
end;
/

What we do here is to run the suite with all the benefits of utPLSQL like running the beforall-procedure but treat it as if we specified the --%rollback(manual) annotation, leaving all data changes as is, not rolling them back.

The content of the room_inventory-table is now like that:

Now we get a pretty good understanding of what the “observable behaviour” is – and can even write a quick expectation for what we expect in the columns ROOM_ID, ITEMand NR_IN_ROOM(ID is irrelevant for our test case).

Please remember to manually rollback before changing your test-package, though!

create or replace package body ut_room_inventory as
  ...
  procedure add_item
  as
    c_actual sys_refcursor;
    c_expect sys_refcursor;
    begin
      room_util.add_item('Light saber (red)', 'TEST');
      room_util.add_item('Light saber (blue)', 'TEST');
      room_util.add_item('Light saber (green)', 'TEST');

      open c_actual for
        select item, nr_in_room from room_inventory where room_id = -1
        order by id;
      open c_expect for
        select 'Light saber (red)' item, 1 nr_in_room from dual union all
        select 'Light saber (blue)'    , 2            from dual union all
        select 'Light saber (green)'   , 3            from dual;

      ut.expect(c_actual).to_equal(c_expect);
    end;
end;
/

We now have a first automated self-test and are pretty sure to not mess up the basic functionality of adding things to a room’s inventory when diving deeper and change some internals.

Thanks OGB!

I did a utPLSQL example for my OGB Appreciation Day post on purpose.

When I started using utPLSQL 2.5 years ago, I couldn’t imagine that it would be the beginning of an awesome journey. Encouraged and uplifted by an immensely kind community I suddenly got in contact with people I only knew from the author page of famous tech-books.

I learned how to contribute to open source, how to develop PL/SQL in a modern, readable, maintainable way, I did my first public talks at meetups and also the big conference stage.

But most importantly: I met incredible, kind and passionate people who love to pass the knowledge and use their experience to uplift and mentor newcomers instead of keeping them out of their ivory tower.

Oracle as a company might not have the best public standing, but I can witness from heart that the community I learned to know is simply awesome and the tools provided, be it AskTom, DevGym or the many, many regional user groups, are something I really wish other technologies could provide, too.

Thank you everyone who makes this community so welcoming and helpful!