NULL is NULL??? NULL is NULL!!!

I remember when this tweet popped up back in December of 2009:

APEX Poll: Do you like the existing %null% behavior of select lists? Do you use it in your applications?

The tweet was hinting that one of the oddities that had existed in APEX for quite a while may soon be a thing of the past. If you’re not sure as to what exactly this tweet was referring, have a look at either of these posts:

http://www.inside-oracle-apex.com/display-null-yes-and-null-problem/

http://www.talkapex.com/2009/07/how-to-resolve-null-issue-in-apex-lovs.html

The fact was that if you created an item that used a List of Values (LOV) and you set the Display Null Value option to Yes, but didn’t specify a Null Return Value – in other words you left the field NULL – then a default value of “%null%” was supplied for you. The problem was, as you’ve probably already concluded, that “%null%'” != NULL.

Many people just accepted this as “the way it works” and then figured out a workaround or adopted one of many generic solutions to fix the problem. However, this is no longer necessary as it’s been fixed in APEX 4.0. Now if you use the Display Null Value option with an LOV and don’t specify a Null Return Value, then NULL is what you will get. It’s a beautiful thing – NULL = NULL! You can’t see it, but I’m doing my happy dance ;)

For those of you worried about backward compatibility, the new feature was implemented in such a way that in will only affect things going forward. Also, if you look under “Home > Application Builder > Application 999 > Utilities > Upgrade Application”, you’ll see an option to convert an application in bulk: “Remove %null% in LOV Null Return Value of Page Items”.