Our customer complained that data was sometimes not showing up in one of our applications. A developer on our team researched the problem and found that it occurred only when the data had leading spaces. His solution was to modify the retrieval Pro*C code to trim out the spaces. On the surface that sounded like an acceptable solution. So he changed the code from
SELECT NVL(text, ‘ ‘)
INTO v_text;
to
SELECT TRIM(NVL(text,’ ‘)
INTO v_text;
The result was that the text that had leading spaces was displayed without the spaces. The problem is that this made the application through an ORA-1405 exception for most cases where there was no text. This was a difficult problem to diagnose. The SQL ran fine in SQL*Plus and PL/SQL Developer.
I was the one who stepped in and debugged this problem. The way I found that this change was the problem was by reviewing the source code change history. The text column was NULL for most records. The NVL turned it into a space. However the TRIM caused the space to revert back to an empty string (NULL). Pro*C did not like putting such a value into the variable. The result was the exception we were getting.
My initial fix was to back out the changes and ship our release. Then I thought maybe we could add one extra NVL around the whole clause to ensure the fix goes in, but it does not break the normal case. The problem is that it was starting to look like some ugly code. I am thinking our eventual fix would be to modify the application to be able to handle text that with leading spaces. Nevertheless this was an interesting exercise in debugging. Another developer spent the weekend trying to figure out what was going on.
