Wednesday, August 10, 2005

Named Notation Parameters in easy to read unit test shock!

One of the better kept secrets of Oracle is the ability to use 'named parameter passing' over 'positional parameter passing'. Oracle covers it here.

That is, if I want to call a procedure that has three parameters that have defaults, and I want to a value pass into the third parameter, I can do so by referencing the name of the third parameter….

Give the function definition:
    FUNCTION insert_employee( pn_department_id  NUMBER := NULL
, pn_manager_id NUMBER := NULL
, pc_employee_name VARCHAR2 ) RETURN NUMBER;

Can be called by performing the following:
    vn_employee_id := insert_employee( pc_employee_name => 'Rob Baillie' );

OK, so in the example given it may make a lot more sense to just order the parameters a little better, like have the employee name first, but you get the idea.

So, it looks cool, and you may be able to think of a few instances where it may prove useful. It can be handy if you think your procedure signatures are likely to change and you have an out parameter you want to keep to the end of the procedure definition...

    FUNCTION insert_employee( pc_employee_name IN     VARCHAR2 
, pn_department_id IN NUMBER := NULL
, pn_manager_id IN NUMBER := NULL
, pc_error_message OUT VARCHAR2 ) RETURN NUMBER;

It can be regarded as tidy make sure that error message parameter is at the bottom, but without having named parameter notation you would never get the benefit of the default parameters, and would have to change all your calls if you ever added a new field between the department and manager id.

Quite handy, but nothing really earth shattering.

The place where we’ve found the most use is in unit tests...

Lets say we’re writing a test for the function:

    FUNCTION insert_holiday( pn_employee_id   IN     NUMBER
, pd_from_date IN DATE
, pd_to_date IN DATE
, pc_error_message OUT VARCHAR2 ) RETURN BOOLEAN;

Lets say that this function has many different ways it can fail: Any of the parameters being NULL, the from data being later than the to date, the total number of days over the allocation...

In each case the function will fail in the same way. Fail to insert the holiday record, return FALSE and set pc_error_message to the reason why it failed.

In order to test for this conditions we write a simple failure check procedure with a signature along the lines of:
    PROCEDURE check_insert_holiday_fails( pc_context      VARCHAR2
, pn_employee_id NUMBER := 7438
, pd_from_date DATE := TRUNC( SYSDATE ) + 10
, pd_to_date DATE := TRUNC( SYSDATE ) + 17 )

Each of the insert_holiday’s input parameters are duplicated on the check procedure, and set to be valid values for that procedure.
Check_insert_holiday_fails calls insert_holiday with the passed in values and goes on to check for the correct three error conditions: No increase in number of holiday records, returning false and passing back an error message.

The pc_context is appended to each of the assertion texts in order to give a nice readout. So, for example, the check false assertion may be (using UtPlsql):

    utAssert.this( 'When ' || pc_context ||', insert_holiday returns false', NOT vb_result );

The idea is that if the procedure was called with only the context value specifed, the call to the tested insert_holiday function would be successful (and the test would fail, if that makes sense!).

So, we have a single procedure that will check that a failure state is returned, covering all the components. We can then call this with our error states, using the named parameter notation in order to only change the parameters we are interested in changing. E.G.

    check_insert_holiday_fails( 'invalid employee_id'    , pn_employee_id => -1 );
check_insert_holiday_fails( 'NULL employee_id' , pn_employee_id => NULL );
check_insert_holiday_fails( 'from date after to date', pd_from_date => SYSDATE + 5, pd_to_date => SYSDATE + 2 );
check_insert_holiday_fails( 'from date in past' , pd_from_date => SYSDATE - 1 );
check_insert_holiday_fails( 'to date in past' , pd_to_date => SYSDATE - 1 );

The use of the named notation means that only the parameters important to the failure are stated, rather than the full list. This makes it easier to see each individual test case clearly. If the procedure being tested has a lot of parameters, then the advantage becomes very clear!

Technorati Tags: , , , , , , ,

Update: Sorry for the chnage in permalink address. I just couldn't handle the typo in the title!


Anonymous said...

This is an invaluable feature, one I've used for years. Now, as I begin porting legacy applications that make use of literally hundreds of stored procedures to the JDev, I discover that there's no way to use named parameters. In a word, ARGHHHH!

Eddie Awad said...

change all your calls if you ever added a new field

Here is a trick to avoid changing all your calls.

We had one procedure like this:

my_proc (
param1_in in number,
param2_in in number,
param3_out out number

This procedure is called from many other places like this:

my_proc (1,2, value_out)

One day, we had to add a new IN parameter to my_proc. So, how to do it without modifying every single call to my_proc? Well, we found out that by adding this new IN parameter to the end of the procedure's parameter list and default it to NULL, did the trick:

my_proc (
param1_in in number,
param2_in in number,
param3_out out number,
param4_in in number DEFAULT NULL

This did not break the calls to my_proc and was able to still use param4_in when required. Of course, non of the calls to my_proc used "notational parameters" calling.

Rob Baillie said...

Aye. Sorry, I skipped over that point a little. This is a common rule, and one that I've seen in many places I've worked. If you need to add a parameter, add it to the end and put a default on in. It definitely works!

However, I have worked at places where its been overused. Put simply: if you have a small enough project to be able to keep track of the codebase properly; if you've got a system that is covered by good automated tests; if you've managed to ring fence your applications so that the interfaces between them are well defined, then there is no reason why you shouldn't be able to change the signature of a procedure and deal with the required changes to the calling procedures.
In the long run this is a good idea as it keeps your procedure signatures much cleaner.