#100CodeExamples – Unit-Tests for Primary/Secondary Lookup with Cursor Comparison
Tags: 100codeexamples, oracle, utplsql
Published: 2018-12-06 22:50:33

---
title: #100CodeExamples – Unit-Tests for Primary/Secondary Lookup with Cursor Comparison
published: true
tags: #100CodeExamples,Oracle,utPLSQL
canonical_url: https://cleandatabase.wordpress.com/2018/12/06/100codeexamples-unit-tests-for-primary-secondary-lookup-with-cursor-comparison/
---

To be sure the [Lookup for Primary/Secondary Power-Nodes](https://dev.to/pesse/100codeexamples--a-challenge-to-learn-and-teach-primaryreplica-lookup-knk) of our Deathstar works correctly and keeps working in future changes of our application, we want to have some basic unit-tests, using the cursor-comparison capabilities of [utPLSQL](http://utplsql.org).

Test-Package Header:

```sql
create or replace package ut_deathstar_power_nodes as
 
  -- %suite(Deathstar power nodes - Primary/Secondary lookup)
  -- %suitepath(deathstar.powerNodes)
 
  -- %test(Get a result even if there is no secondary power node)
  procedure get_result_for_only_one_entry;
 
  -- %test(Get whole group of related power nodes, no matter which node-ID queried)
  procedure get_whole_group_multiple_secondaries;
 
end;
/
```

Test-Package Body:

```sql
create or replace package body ut_deathstar_power_nodes as
 
  function cursor_actual( i_power_node_id integer )
    return sys_refcursor
  as
    c_result sys_refcursor;
    begin
      -- Just get all results from our View-under-test
      -- for a given ID
      open c_result for
        select *
          from v_deathstar_grouped_power_nodes
          where power_node_id = i_power_node_id
          -- Order of rows is important for comparison
          order by member_id desc;
 
      return c_result;
    end;
 
  procedure get_result_for_only_one_entry
  as
    c_expected sys_refcursor;
    begin
      -- Arrange: Make sure we have proper test-data
      -- available, using negative primary keys.
      -- Will be rolled back after test
      insert into deathstar_power_nodes(id, label, primary_node_fk)
        values ( -1, 'Primary 1', null );
 
      -- Assert via cursor comparison
      -- Create expectation-cursor
      open c_expected for
        select
          -1 power_node_id,
          -1 group_id,
          -1 member_id,
          'Primary 1' member_label,
          1 is_primary
        from dual;
 
      -- Compare the two cursors
      ut.expect(cursor_actual(-1))
        .to_equal(c_expected);
    end;
 
  procedure get_whole_group_multiple_secondaries
  as
    -- We need a fresh cursor for each expectation,
    -- so we put it in a sub-function we can easily call
    function cursor_expected return sys_refcursor
    as
      c_expected sys_refcursor;
      begin
        -- Define Expectation-Cursor for this test
        open c_expected for
          -- Column-aliases are only necessary on
          -- first select
          select
            null power_node_id,
            -1 group_id,
            -1 member_id,
            'Primary 1' member_label,
            1 is_primary
          from dual
          union all select null, -1, -2, 'Secondary 1', 0 from dual
          union all select null, -1, -3, 'Secondary 2', 0 from dual;
 
        return c_expected;
      end;
 
    begin
      -- Arrange
      insert into deathstar_power_nodes(id, label, primary_node_fk)
        values ( -1, 'Primary 1', null );
      insert into deathstar_power_nodes(id, label, primary_node_fk)
        values ( -2, 'Secondary 1', -1 );
      insert into deathstar_power_nodes(id, label, primary_node_fk)
        values ( -3, 'Secondary 2', -1 );
 
      -- Assert
      -- Check first entry of testdata: Primary 1
      ut.expect(cursor_actual(-1))
        .to_equal(cursor_expected())
        -- Ignore column POWER_NODE_ID, because it doesn't matter
        -- That way we can use the same expectation cursor
        -- for all checks
        .exclude('POWER_NODE_ID');
 
      -- Check Secondary 1
      ut.expect(cursor_actual(-2))
        .to_equal(cursor_expected())
        .exclude('POWER_NODE_ID');
 
      -- Check Secondary 2
      ut.expect(cursor_actual(-3))
        .to_equal(cursor_expected())
        .exclude('POWER_NODE_ID');
 
    end;
 
end;
/
```

Test-Call:

```sql
call ut.run('ut_deathstar_power_nodes');
```

Unfortunately I can not provide a LiveSQL example, but you can find a complete SQL script [here](https://github.com/pesse/sith-demo-db/blob/master/100CodeExamples/2_deathstar_power_nodes_incl_tests.sql).  
The script contains creation of necessary DEATHSTAR\_POWER\_NODES-Table and view, installation of test package and cleanup of all objects.

Run it in any environment with utPLSQL installed for a working example.