Unit Tests and Oracle

Our organization has relatively recently jumped on the bandwagon and started testing our code. Fucking weird, I know. Why test your code, you say? Aren't we all super brilliant and everything we right comes out the write way? Well, as you can tell by me switching the right and write there, sometimes shit happens. Even the best authors have typos now and then and even the best coders misplace a semi-colon from time to time. Yeah, the compiler will probably catch the bogus semi-colon but sometimes we have errors in logic as well. I know, I know. It sucks to say. Coders are human and humans are great and terrible and everything in between. And sometimes those errors in logic are so subtle that you might not find it until it is live, and you might not find it until some customer points it out and then you have angry managers and egg on your face and have to endure the snickers and giggles of your co-workers and not just when they catch a glimpse at the urinal. So, some genius somewhere invented the idea of testing your code. This happens in java and dot net by faking a picture of the application and supplying everything that is needed to run a scenario through and compare the output against your expected results. In the database, it is a little bit different. I can not always substitute something fake for something real. Often times, I have to actually enter data into the tables and then delete it afterwards.

So, anyway, all of the other fucktard coders here are implementing unit tests or at least faking it. I didn't want to be left out and didn't really have a choice in the matter. There are at least three ways to do this. One is to write everything yourself. Code setup procedures and tear down procedures and test procedures and run the tests. Two is to find some product to assist you. Three is to do a little bit of both. I have settled on the third way at this point. I wrote my own tests for the first couple of objects before I found the UTPLSQL package written by Steven Feuerstein. It was freeware so I grabbed it and took a look. It is a pretty interesting set of code but since most of the objects I am testing are stored procedures for reporting, I found the asserts in the UTPLSQL a bit limiting. So, naturally, I figured I would use as much as I could and make up the rest on my own. It has worked okay so far.

Unit tests are self-contained objects that mimic everything but the subject of the test and then compare results received from the test subject against expected results. Let's imagine our program is a calculator. It would be relatively easy to test that 1 + 2 = 3 and such. In reality, I work with a transaction processing system and the reports can be quite complicated to test. I insert a new record for every table involved in the report query and compare the results of the stored procedure. For our calculator program we might issue something like: assertTrue(calc(1,2,'+'), 3); however, my report procedures return the data set in a cursor. It looked like the UTPLSQL had an assert that would handle cursors but I had a looming deadline and a few minutes with it wasn't enough for me to figure it out so I panicked and wrote my own cursor assert. I write a package for each report I need to test that contains a setup procedure ut_setup, a tear down procedure ut_teardown, a test procedure ut_rpt_to_test where the name has ut_prepended to the report stored procedure name, and a shit ton of constants. The constants are the string literals that I insert during setup and delete during tear down and compare against the results during the test.

It is still a little bit foreign to me and I'm still learning new and better ways to test. Each one gets a little easier. If you have to work with Oracle and are looking to bulletproof your code, check out UTPLSQL. There may be others out there as well. There are probably suites for other popular databases as well, but you may prefer to home-cook your own. So, go ahead you fucking rebel! Be a Tom Petty! You won't back down from unit testing! But, if HB 499 passes you might get another dance with Mary Jane. Am I right? Nudge nudge wink wink, say no more Squire!

I may get into the nuts and bolts of the tests in a future post and some of you fucking rock star coders might show this humble database halfwit a thing or two with your fancy hipster unit testing. You probably did it before it was cool. Goddamn hipsters! Get off my lawn!

Until next time.

Accepting multiple values in a parameter

I had an interesting case recently where I had to modify a report to accept a parameter that could be a comma separated list of values and I needed to filter on the value(s). Someone on the system before me created a function called stringsplit that helps this occur. Her is the form of stringsplit:

CREATE OR REPLACE FUNCTION STRINGSPLIT (expression varchar2, delimiter varchar2 := ',')

return SPLIT_TABLE pipelined

IS

    idx    pls_integer;

    listTmp    varchar2(32767) := expression;

BEGIN

    loop

        idx := instr(listTmp,delimiter);

        

        if idx > 0 then

            pipe row(substr(listTmp, 1, idx-1));

            listTmp := substr(listTmp, idx+length(delimiter));

        else

            pipe row(listTmp);

            exit;

        end if;

    end loop;

    return;

END STRINGSPLIT;

As you can see, this function returns a SPLIT_TABLE type. The form of the Split_Table:

CREATE OR REPLACE TYPE SPLIT_TABLE AS TABLE OF VARCHAR2(32767);

So, our type is a table of large varchar2 values and our function takes a comma separated list and returns a table of those values. Now, we need to reference and conditionally filter on this in our report query. The condition is that if it is null, do not filter, otherwise filter. It would look something like:

CREATE OR REPLACE PROCEDURE myRecipeReport (

   recipeTypeList   IN     VARCHAR2 DEFAULT NULL,

   result_cursor       OUT SYS_REFCURSOR)

IS

BEGIN

   OPEN result_cursor FOR

      SELECT r.name

        FROM recipes r

       WHERE (   recipeTypeList IS NULL

              OR (EXISTS

                     (SELECT TO_NUMBER (COLUMN_VALUE)

                        FROM TABLE (STRINGSPLIT (recipeTypeList, ','))

                       WHERE TO_NUMBER (COLUMN_VALUE) = r.type)))

      ORDER BY 1;

END myRecipeReport;

This allows us to run the report for everything (list parameter is null) or filter on a subset of available types.

We are getting into several new things here: Types, Casting, etc. Google anything that you are interested in that I do not cover explicitly. 

Types:

A Type is a user defined method to model the structure and behavior of data in an application. You can create object types and static array types and as we've seen, table types. These are usually collections of data that are used somewhere in your application. You will need the Create Type privilege to create a type in your schema, or Create Any Type to create a type in a different schema. 

Casting:

As you may be aware from other coding ventures, Casting converts one datatype to another. 

Pipelined:

You specify Pipelined on a function when you want the function to be the source of data instead of a table. In our purpose here, we are calling the stringsplit function on a single variable and creating a table out of that.

Pipe row:

Pipe row is an Oracle function that is used in the pipelined function to return the results in a table format.

Select from Table:

This is how you treat the results of the pipelined function.

Pls_integer:

This is a pl/sql datatype in the NUMBER family that ranges from -2,147,483,647 to 2,147,483,647. It uses machine arithmetic and is usually faster in operations than the sql datatype NUMBER.

Column_value:

This is how you refer to the column returned from the pipelined function in the table format.

Exists:

This is a native oracle function that executes a subquery and returns true or false depending on whether or not rows were found. If the subquery refers to a column in the parent query it is referred to as a correlated subquery.

I feel I must apologize for the dearth of fucks and shits and goddamnits in this post. I must be off my game today. I am still rather disgruntled and somewhat riled up at the absurdities and idiocy that constantly surround me. I think I must have just focused on getting the information out this time. I will redouble my efforts to include references to coat-hanger abortions and liken my fucktard predecessors to Corky from Life Goes On, Client #9 and/or Justin Beiber's presumably tiny hairless nutsack. Until next time, keep fighting the good fight, stockpiling weaponry, practicing your tomahawk moves, preparing for the zombie apocalypse or civilization collapse and just whatever general badassery you are into. Thanks Obama! ;)