First, for most of us (“us” includes me), testing really comes down to “Let’s try a few things and get confidence that I can add it to the next deployment without any major embarrassments.”

Sadly, trying is not testing. Trying things, ad hoc, makes it all too easy to miss bugs, even miss the fact that you haven’t implemented all the requirements.

I therefore offer the following.

Before you start writing the program, ask yourself the question:

How will I know when I’m done?

It’s a simple question, an obvious one. You are writing code, and then at some point, you will stop. You finished. How do you know when you’re finished?

Ask yourself this question and then write down the answers in the form of a list of what are, in essence, test scenarios.

Next, ask yourself another key question:

What assumptions am I making?

We always make assumptions when writing a program. Assumptions like “The department ID will never be null.” and “There will always be data in the table.” and “The hire date will always be in the past.”

It’s OK to make assumptions, but you should verify that they have not been violated before you charge into the code that implements program requirements.

Make a list of your assumptions.

“Finally” you can get started writing the program. At the very start of the program, verify your assumptions. The best and easiest way to do that is to use an assertion package. I offer a basic one on Oracle LiveSQL: https://livesql.oracle.com/apex/livesql/file/content_CFLUGC2RAJKP3RAF5XYUMTXE8.html

Then as you write your program, check your “Am I Done?” list. Heck, you might even go ahead and put that list in your package as a comment, right next to or inside the program itself. When everything on the list is covered, it’s time to make sure it compiles   and then start testing.

Oh, right. Back to that. Testing. Here’s my “on the cheap” suggestion for testing: build yourself a simple test harness.

Of course, this is not always going to be easy or possible. If you are working on a very big, complicated procedure, testing will be a big, complicated task.

This means, more than anything else, that you should break up that big, complicated procedure into smaller units that can be tested independently and more easily.

Regardless, I do believe that in many cases, you can do something that takes you closer to the ideal of a repeatable, automated regression test. And anything is better than nothing.

I know: talk is cheap. Fine, let’s take a look at an example. Suppose I am building an application that keeps track of Environmental Protection Agency investigations regarding the spraying of pesticides on land. (note: I have made this up. I have no idea if the EPA actually does this. Clearly, it should.)

For each investigation, there is a details table:

create table investigation_details (
   id number generated always as identity,
   investigation_id number,
   spray_id number,
   pesticide_id number)
/

And then, of course, things get messy, by which I mean:

• A spraying (spray_id) need not be associated with a pesticide (pesticide_id) in the same row.
• If a user adds a spray_id to a given investigation, then we need to first see if there are any rows in the table for that investigation in which the spray_id is null. In that case, update that row with the spray_id.
• Same thing for pesticide.

In other words (1), I need to “pack” the IDs into existing rows if possible. If not, then insert a new row. Of course, that doesn’t make any sense. There should be two different “child” tables, one for sprayings for that investigation and another for pesticides. But then again, sometimes the spraying and the pesticide are related.

In other words (2), the data model is a mess and we cannot change it for this little project. Just like a gazillion of the projects we have all worked on over the years.

So “pack the rows”, it will be. Now, to demonstrate a “cheap testing” approach, you don’t really need to see the details of the packing routine. However, in case you want to check it out (and probably offer better alternative implementations), you’ll find it at the bottom of this article.

All we need to know for the test harness, though, is the API, so here’s the package spec:

package investigation_mgr authid definer
is
   procedure pack_details (
      p_investigation_id  in  number,
      p_spray_id          in  number,
      p_pesticide_id      in  number
   );
end;

But before I can test, I need to answer the question (and come up with my test scenarios):

How will I know when I’m done?

The pack_details procedure needs to handle these scenarios:

• If there is no row in the table for the investigation ID, then insert a new row with the spray and pesticide IDs set in them.
• If there is a row for this investigation ID, where spray_id is null, and p_spray_id is not null, update that row with the ID.
• If there is a row for this investigation ID, where pesticide_id is null, and p_pesticide_id is not null, update that row with the ID.

That’s surely not a comprehensive list, but good enough for our demonstration. So let’s build a test harness just for this procedure. The advantage of a purpose-built harness like this one is that I can fashion the verification code to fit the requirements tightly.

In this case, after the procedure is run, I need to examine the contents of the details table to see if it matches my expectations.

There are four possible “states” of a row for an investigation:

• There is no row for that investigation
• Both spray and pesticide IDs are not null
• Only spray ID is not null
• Only pesticide ID is not null

I can write a query to test for each of these:

select count(*)
  from investigation_details
 where investigation_id = p_investigation_id;

select count(*)
  from investigation_details
 where investigation_id = p_investigation_id
   and pesticide_id is null
   and spray_id is null;

select count(*)
  from investigation_details
 where investigation_id = p_investigation_id
   and pesticide_id is null
   and spray_id is not null;

select count(*)
  from investigation_details
 where investigation_id = p_investigation_id
   and pesticide_id is not null
   and spray_id is null;

So, conceptually, here’s how any test of the packing routine would work:

1. Set up the details table for that investigation ID (such as: delete all rows, just have one row with all non-null values, etc.)
2. Run the procedure for a given set of inputs (example: new investigation ID, spray id not null, pesticide Id null)
3. Run query afterward to see if the rows for that investigation ID match the expected count.

I can combine all four of the above queries into a single query to do this, also taking into account the parameter values passed to the packing procedure:

create or replace package test_harness authid definer
/* test harness for ruling details */ is

   procedure initialize (p_investigation_id in number);

   procedure check_results (
      p_desc                  in  varchar2,
      p_investigation_id      in  number,
      p_number_of_rows        in  number,
      p_pesticide_id_is_null  in  varchar2,
      p_spray_id_is_null      in  varchar2
   );
end;
/

create or replace package body test_harness is
   procedure initialize (p_investigation_id in number) is
   begin
      delete from investigation_details
       where investigation_id = p_investigation_id;
   end;

   procedure check_results (
      p_desc                  in  varchar2,
      p_investigation_id      in  number,
      p_number_of_rows        in  number,
      p_pesticide_id_is_null  in  varchar2,
      p_spray_id_is_null      in  varchar2
   ) is
      l_count integer;
   begin
      select count(*)
        into l_count
        from investigation_details
       where investigation_id = p_investigation_id
         and ( ( pesticide_id is null and p_pesticide_id_is_null = 'Y' )
               or 
               ( pesticide_id is not null and p_pesticide_id_is_null = 'N' ) )
         and ( ( spray_id is null and p_spray_id_is_null = 'Y' )
               or ( spray_id is not null and p_spray_id_is_null = 'N' ) );

      dbms_output.put_line(p_desc
                           || ': '
                           || case l_count when p_number_of_rows then  'SUCCESS'
                                   else 'FAILURE' end);
   end;
end;
/

Here’s how I would then use this verification routine:

declare
   l_investigation_id integer := 100;
begin
   /* Remove any rows for this investigation ID */
   test_harness.initialize(p_investigation_id => l_investigation_id);
   
   investigation_mgr.pack_details(
      p_investigation_id  => l_investigation_id,
      p_pesticide_id      => 123,
      p_spray_id          => 789
   );

   test_harness.check_results(
      p_desc                  => 'all three provided, no rows in table',
      p_investigation_id      => l_investigation_id,
      p_number_of_rows        => 1,
      p_pesticide_id_is_null  => 'N',
      p_spray_id_is_null      => 'N'
   );

   investigation_mgr.pack_details(
      p_investigation_id  => l_investigation_id,
      p_pesticide_id      => 123,
      p_spray_id          => 789
   );

   test_harness.check_results(
      p_desc                  => 'all three provided, 1 row in table',
      p_investigation_id      => l_investigation_id,
      p_number_of_rows        => 2,
      p_pesticide_id_is_null  => 'N',
      p_spray_id_is_null      => 'N'
   );

   investigation_mgr.pack_details(
      p_investigation_id  => l_investigation_id,
      p_pesticide_id      => null,
      p_spray_id          => 789
   );

   test_harness.check_results(
      p_desc                  => 'all three provided, 1 row in table',
      p_investigation_id      => l_investigation_id,
      p_number_of_rows        => 1,
      p_pesticide_id_is_null  => 'Y',
      p_spray_id_is_null      => 'N'
   );
end;

Time to Explore

Here’s a blog post I wrote a while ago that goes into more depth on the idea of a checklist.

http://stevenfeuersteinonplsql.blogspot.com/2015/06/checklist-driven-development-tdd-on.html

The packing procedure

package body investigation_mgr

is

   procedure pack_details (

      p_investigation_id  in  number,

      p_spray_id          in  number,

      p_pesticide_id      in  number

   ) is

      /* all the rows for this investigation_id */

      cursor investigation_cur is

         select id,

                spray_id,

                pesticide_id

           from investigation_details

          where investigation_id = p_investigation_id;




      /* This will hold the parameter values. */

      l_ids_from_app          investigation_cur%rowtype;




      /* This one holds the values from a row in the table */

      l_investigation_detail  investigation_cur%rowtype;




      /* Control loop through matching detail rows and whether or not to update */

      l_done                  boolean := false;

      l_do_update             boolean := false;




      procedure initialize_local_ids is

         l_count integer;

      begin

      /* Copy the parameter values to this local record. That way, I can override

         the values with null after I've put them into the table, and know that

         I am getting closer to being done.




         BUT also set the ID to null if it is already in the table for this

         investigation ID.

      */

         select count(*)

           into l_count

           from investigation_details

          where investigation_id = p_investigation_id

            and pesticide_id = p_pesticide_id;




         l_ids_from_app.pesticide_id    :=

            case

               when l_count = 0 then

                  p_pesticide_id

            end;

         select count(*)

           into l_count

           from investigation_details

          where investigation_id = p_investigation_id

            and spray_id = p_spray_id;




         l_ids_from_app.spray_id        :=

            case

               when l_count = 0 then

                  p_spray_id

            end;

      end;




      procedure check_for_update (

         p_id_from_app    in out  number,

         p_id_for_update  in out  number,

         p_do_update      in out  boolean

      ) is

      begin

         if p_id_from_app is not null and p_id_for_update is null then

            p_do_update      := true;

            p_id_for_update  := p_id_from_app;

             /* We are going to update with this value, so mark it as "done" */

            p_id_from_app    := null;

         end if;

      end;




      procedure insert_if_needed (

         p_ids_from_app in investigation_cur%rowtype

      ) is

      begin

         /* If at least one ID still needs to be stored, time to insert.

            Note that any columns that don't have an id "left" are set to null,

            so they can be "packed" later */

         if p_ids_from_app.pesticide_id is not null or p_ids_from_app.spray_id is not null then

            insert into investigation_details (

               investigation_id,

               spray_id,

               pesticide_id

            ) values (

               p_investigation_id,

               p_ids_from_app.spray_id,

               p_ids_from_app.pesticide_id

            );

         end if;

      end;

   begin
      initialize_local_ids;
      if p_investigation_id is not null then
         open investigation_cur;
         while not l_done loop
            fetch investigation_cur into l_investigation_detail;
            l_done := investigation_cur%notfound;

            if not l_done then
               /* Can we update any null columns in the currently fetched row? */
               check_for_update(l_ids_from_app.pesticide_id, l_investigation_detail.pesticide_id, l_do_update);
               check_for_update(l_ids_from_app.spray_id, l_investigation_detail.spray_id, l_do_update);

               /* If there is at least one column that can be updated, we update all three,
                  BUT we update the column to its current value (see check_for_update for this logic). */

               if l_do_update then
                  update investigation_details
                     set pesticide_id = l_investigation_detail.pesticide_id,
                         spray_id = l_investigation_detail.spray_id
                   where id = l_investigation_detail.id;

                  l_do_update := false;
               end if;

               /* If we've taken care of all the IDs passed into the procedure, we can stop.
                  No insert will be needed. We could also exit the loop because no more rows
                  are found. In which pesticide, an insert *will* be performed if at least one
                  of the IDs are still not null. */

               l_done :=
                  l_ids_from_app.pesticide_id is null and l_ids_from_app.spray_id is null;
            end if;
         end loop;
         close investigation_cur;

         insert_if_needed(l_ids_from_app);
      end if;
   end;
end;