It was the night before the big release to our customer. Our internal test team was still finding some bugs in our software. Ouch that’s painful. One such error being reported was an ORA-01403 when they brought up a new screen. This was perplexing. I thought every single column in the SELECT statement was wrapped with the NVL function. And I knew there was a record in the database that we were selecting from. What the heck was going on?Well the best way to debug weird bugs like this is to try things out. I knew there were some NULL column values in the record the testers were selecting. So I updated some test data and filled in some values. The problem went away. Was I losing my mind? Or perhaps the NVL was broken. I did not really write this code from scratch. I copy and pasted it from another application. It should have worked.
That’s when it hit me. The guilty column was defined as evilCol CHAR(1). However the column being selected was written as NVL(evilCol, ‘NA’). Do you see the problem? The default for NULL values of the column was one which was too large to fit in the column given its definition. Oracle won’t replace a NULL with something that is too large to fit in the source column. Thus I got a SQL warning that we had a NULL issue.
Here is the real shame about this bug. I had previously fixed a bad NVL default for another column being selected in this SQL statement. However I did not go through every column to check whether there were other bad defaults. Shame on me. Luckily I went through all the column and their NVL defaults this time. The hard lesson was learned. Be careful of the size (and type) of defaults you return using the NVL function. They might not match the column type you are selecting from.
