Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Mon, 27 Aug 2007 10:05:56 +0200
Message-ID: <46d286e2$0$1347$834e42db@reader.greatnowhere.com>

DA Morgan wrote:
> William Robertson wrote:

>> On Aug 26, 2:17 pm, Frank van Bortel <frank.van.bor..._at_gmail.com>
>> wrote:
>>> NULL <> NULL, but '' = '', if that would help.
>>> The length of NULL is NULL again.
>>
>> If we had a '' in Oracle then its length would be 0, but we don't,
>> which is the subject of this thread. I was hoping someone could
>> provide an example of non-null '' being a useful value.

>
> You won't find one. What you will find is that people who have it
> as an option create designs that use it. Those that don't have it
> as an option don't. In the end you only have three values, NULL, 0
> and > 0. There are a nearly unlimited ways to code this in any
> language and in any tool.

I think you are right in that you can design both ways and I also think that it is probably really hard to find a case where '' means anything useful different from NULL from a semantic point of view.

However, the point is was trying to make in my posts was that most systems/programming languages make the distinction and in general string processing the distinction is also very useful ( LENGTH(A+B) === LENGTH(A) + LENGTH(B) ).
So the behavior of Oracle just makes the usage more complicated and error prone, imho.

br,
Martin Received on Mon Aug 27 2007 - 03:05:56 CDT