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)
IS
  l_id   INTEGER;
BEGIN
  assert.
   is_true (
     CASE
        WHEN item_in IS NOT NULL
        THEN
           building_in IS NOT NULL AND room_in IS NOT NULL
        WHEN room_in IS NOT NULL
        THEN
           building_in IS NOT NULL
        ELSE
           building_in IS NOT NULL
     END
   , '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:

BEGIN
  process_data (building_in => 1, room_in => NULL, item_in => 2);
END;
/

ORA-20000: ASSERTION VIOLATION! All arguments from highest to lowest key values must be NOT NULL.

Thanks for pointing this out, Carl! SF