Record Types

Today is the last day for a developer on our project. He moved his family away, and has a new job far away. The company asked him to fix one troubling bug before he left. He was going crazy and asked me for some help. He set up some debugging in a PL/SQL package where he dumped out some contents to a log file. But they were just not making sense.

I took a look at his log. One of the variables was zero. He insisted that the source column for that variable was non-zero in the database. Then he started randomly asking whether this or that was the cause of the problem. I told him we should not guess, and inspect the place where the variable is initialized.

I found a nice routine which loaded the data from a table. It puts it into an object which is a custom record type. We select a bunch of fields into this object. The order of the select clause is supposed to match the order of records in the field. So I checked the one field in question. Oh oh. It was out of alignment.

Somebody added a new field at the end of the record. However the put the query for this new field in the middle of the SQL statement. Bamn. That's the problem. This was problably not a great design. We should make the select more explicit to put the value into fields of the record type. Maybe an idead for a redesign.