24 September 2010

Was 22 September quiz on COALESCE missing critical code? (1401)

The 22 September quiz contained this text: "The procedure below accepts three arguments: the primary key to the building, the primary key to a room in that building, and the primary key of the item in that room...[skipping code]...The arguments passed to process_data always follow these rules:
  • If the item key is not null, then the other two "higher" keys must also be not null.
  • If the room key is not null, the building argument must not be null.
  • The building key is never null.
"In other words, if you pass in values for the building key and the item key, then you must also pass in a non-NULL value for the room key." One player asked: "Shouldn't there be something that validates the input to ensure that all higher elements are not null such that if item is not null, make sure the room and building are not null before returning item as valid?...it does invalidate the quiz, but only from the standpoint that 1 of the requirements is not implemented. Namely, If the item key is not null, then the other two "higher" keys must also be not null. This is not checked for in the function." First, I do not think that this concern invalidates the quiz. I did not say that the program must implement these rules. I said that the "arguments passed to process_data always follow these rules." That is, I define as an assumption that the arguments do not need validation. They will "always follow these rules." Well, that might be fine for a quiz, but of course in the real world, making such an assumption would be a big mistake. Carl raises an excellent point here: any assumptions you make when you write your program should be validated as TRUE before you start running the code that relies on those assumptions. One very nice way to do this is to use assertion routines.You can download an assert package (in assert.pkg file) from my demo.zip. Here's what the procedure might look like with an assertion:
PROCEDURE process_data (
  building_in   IN plch_buildings.building_id%TYPE
, room_in       IN plch_rooms.room_id%TYPE DEFAULT NULL
, item_in       IN plch_items.item_id%TYPE DEFAULT NULL)
  l_id   INTEGER;
   is_true (
        WHEN item_in IS NOT NULL
           building_in IS NOT NULL AND room_in IS NOT NULL
        WHEN room_in IS NOT NULL
           building_in IS NOT NULL
           building_in IS NOT NULL
   , 'All arguments from highest to lowest key values must be NOT NULL.');
  l_id := COALESCE (item_in, room_in, building_in);
  DBMS_OUTPUT.put_line (l_id);
END process_data;
And when I run this program with invalid arguments, I see an error:
  process_data (building_in => 1, room_in => NULL, item_in => 2);

ORA-20000: ASSERTION VIOLATION! All arguments from highest to lowest key values must be NOT NULL.
Thanks for pointing this out, Carl! SF


  1. Strictly speaking, there was no need for the textual description (functionality explanation and usage rules) of the function for this quiz. I thought Steven wrote it as a trick to trap the astonishingly fast answerers!

  2. I really appreciate carl for pointing out this. Checking the input parameters especially possiblility of NULL valued parameters is critically important as they are the main reasons for production fallout. Of course this has no impact on quiz but defenitely worth discussing. In our programming environment we don't have assertion calls, but we do log all the possible critical NULL input parameters with generic error code appended with the package name, method name and the parameter name. Please share your ways of dealing these kind of scenarios out of which the best can be followed as a practice in future.

  3. Yes, this (and many other quizes) in this contest are about technicalities and not about the best programming style. So it may be worth it to make a disclaimer on the assumption page :)