OK, without question, you should be using an automated regression test utility like utPLSQL or integrated unit testing of SQL Developer or Quest Code Tester for Oracle.
That’s what you should do. But you don’t, right? Very few of us do. So I thought that I might offer an idea or two of how you can at least make some improvements “on the cheap” (without a big investment of time and effort) to your current “testing regime.”
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:
- Set up the details table for that investigation ID (such as: delete all rows, just have one row with all non-null values, etc.)
- Run the procedure for a given set of inputs (example: new investigation ID, spray id not null, pesticide Id null)
- 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.
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;