TRIM to NULL


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.

Developer of the Year

This month’s Oracle magazine doled out a bunch of awards. One of them was the PL/SQL Developer of the Year. This year the honor went to Alex De Vergori. He is a database architect for Betfair in London, England.

Alex works on a betting engine with 250k lines of code. It is distributed among computers located around the world. It uses Oracle RAC, as well as Oracle Coherence. There is a middle tier written in Java. The front end uses Ajax. The system processes 5 million transactions a day on average. At peak times it crunches 1000 transactions a second.

I wondered what it took to become PL/SQL developer of the year. Alex stated that you needed an attention to detail. You also need to be passionate and take pride in your software. Somehow I think you might also need to be working with the latest Oracle technologies. To tell you the truth, I have not even heard of Coherence before reading about this award.

Looking back on my own PL/SQL programming career, my biggest year was when I implemented a subsystem of our loads software. It was written mostly in PL/SQL, with a little UNIX Korn shell and a SQL script or two. The production code had about 6k lines of code. However I also coded up a unit test harness that was 5k lines of code by itself.

This may not seem like an abundance of code. The hard part was determining the requirements for the coding. That took so long that the schedule only gave me 4 days to code the whole thing. Let’s just say I knocked out a lot of code that compiled. And it ran super fast. There were a number of trouble tickets written against my code. They all got resolved.

So what should I do? Nominate myself for the award next year? Maybe I should try to contribute to Oracle magazine. No. I think I will just keep posting to my blog here. In fact, perhaps I should give out my own award. What do you think about the “2008 Top Oracle PL/SQL Blogger” award? I hate to tell you this, but the winner is me.