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: Oracle, unit+testing, extreme+programming, software, development, Robert+Baillie, test, testing

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