#100CodeExamples – utPLSQL Cursor Comparison with user-defined Types

I had the pleasure to present utPLSQL to the local DOAG usergroup in Karlsruhe last thursday and one of the questions after the presentation was how we can control the expected results of a cursor-comparison.

We already had an example using SELECT FROM DUAL and UNION ALL, but sometimes our expected outcomes are more complex.

In such situations, we can use user-defined types and the power of Nested Table-collections:

/* Setup base data objects */
create table planets (
  id integer primary key,
  name varchar(256)
);

create table garrisons (
  id integer primary key,
  fk_planet integer not null,
  constraint garrisons_fk_planet foreign key ( fk_planet )
    references planets( id )
);

/* This is the view we want to test, a list of all
   planets and their garrisons (if they have some) */
create or replace view v_planet_garrisons as
select
  p.id planet_id,
  p.name planet_name,
  count(g.id) over (partition by p.id) planet_num_of_garrisons,
  g.id garrison_id
from planets p
  left outer join garrisons g on p.id = g.fk_planet
;

create or replace package ut_garrisons as
  -- %suite(Garrisons)

  -- %beforeall
  procedure setup_planets_and_garrsions;
  -- %test(V_PLANET_GARRISONS returns results for planets with and without garrisons)
  procedure select_v_planet_garrisons;

  /* To select from user-defined types in SQL (so we can use
     cursor-comparison) we need to define them on spec-level
     so they are visible. */
  type t_info_record is record (
    planet_id integer,
    planet_name varchar2(256),
    planet_num_of_garrisons integer,
    garrison_id integer
  );

  /* Nested tables are the only collections that can be accessed
     from SQL */
  type t_info_table is table of t_info_record;
end;
/

create or replace package body ut_garrisons as

  procedure setup_planets_and_garrsions
  as
    begin
      /* Insert test-data with negative primary keys
         so we dont have collisions with existing data */
      insert into planets values (-1, 'Dromund Kaas');
      insert into planets values (-2, 'Korriban');

      /* We want only one planet to have garrisons.
         To better distinct the PKs, we give different to
         the garrisons than to the planets */
      insert into garrisons (id, fk_planet ) values ( -10, -1 );
      insert into garrisons (id, fk_planet ) values ( -11, -1 );
    end;

  /* A little helper-function to get a cursor for
     the current values of our view, limited by entries
     with planet_id < 0, so we only get test-data */
  function cursor_current_planet_garrisons
    return sys_refcursor
  as
    c_result sys_refcursor;
    begin
      open c_result for
        select * from v_planet_garrisons where planet_id < 0
          /* Ordering is very important to get the results
             exactly as we expect them */
          order by planet_id desc, garrison_id desc;
      return c_result;
    end;

  procedure select_v_planet_garrisons
  as
    /* Remember to initialize the table-collection */
    l_expected_table t_info_table := t_info_table();
    c_expected sys_refcursor;
    begin
      /* Populate our table-type with the expected results */
      l_expected_table.extend;
      l_expected_table(1).planet_id := -1;
      l_expected_table(1).planet_name := 'Dromund Kaas';
      l_expected_table(1).planet_num_of_garrisons := 2;
      l_expected_table(1).garrison_id := -10;

      l_expected_table.extend;
      l_expected_table(2).planet_id := -1;
      l_expected_table(2).planet_name := 'Dromund Kaas';
      l_expected_table(2).planet_num_of_garrisons := 2;
      l_expected_table(2).garrison_id := -11;

      l_expected_table.extend;
      l_expected_table(3).planet_id := -2;
      l_expected_table(3).planet_name := 'Korriban';
      l_expected_table(3).planet_num_of_garrisons := 0;
      l_expected_table(3).garrison_id := null;

      /* Open expected-cursor for our populated data */
      open c_expected for
        select * from table(l_expected_table);

      /* Now we can do an easy cursor-comparison */
      ut.expect(cursor_current_planet_garrisons())
        .to_equal(c_expected);
    end;

end;
/

call ut.run('ut_garrisons');

It’s sadly not runnable in LiveSQL, but you can get the whole sourcecode as always on Github.

To see how meaningful the information of the utPLSQL cursor-comparison is, you can just change one of the expected values. Or delete a row.