Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Tue, 21 Aug 2007 08:57:17 +0200
Message-ID: <46ca8d78$0$1345$834e42db@reader.greatnowhere.com>

Ed Prochak wrote:
> On Aug 17, 2:44 pm, "David Portas"
> <REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:

>> "Thomas Kellerer" <FJIFALSDG..._at_spammotel.com> wrote in message
>>
>> news:5im5abF3p468kU1_at_mid.individual.net...
>>
>>
>>
>>>> I don't think that there is a similar "empty" concept for dates.
>>> That's my point. Character seems to be the only data were everybody
>>> requires the distinction between "nothing" and "empty" but nobody has ever
>>> requested this distinction for dates or numbers.
>> The question of whether we choose to refer to some value as "empty" is
>> entirely beside the point in my opinion. The issue is that the domain of
>> string values supported by Oracle is not equivalent to the domain of string
>> values supported by any other DBMS or programming language that I know of.
>> Null is not a value. A string consisting of zero characters IS a value
>> anywhere except Oracle.

>
> Oracle is not the only DBMS with this characteristic.
> UNIFY DataServer has the same "feature".
>
> I don't agree that a string consisting of zero characters IS a value.
>
> Is "" logically different from " "? If I have a user filling out an on-
> line form and they enter nothing into one of the fields, I would load
> a NULL in the DB. Would you really try loading an empty string? What
> would that mean exactly??
>

>> You could "design out" the problem as Daniel Morgan suggests but then you
>> may have to accept that some process or function which potentially has to
>> support zero-length string values can no longer use Oracle as a data store.

>
> Only if "" carries some meaning. I for one don't think it has any
> meaning outside a C program (where it is a one byte array initialized
> with a zero byte value). If you really want "" then you likely want a
> BLOB column.
>

>> Alternatively, you have to compromise by using nulls to represent values or
>> converting the strings to some other data type or representation.
>>
>> --
>> David Portas

>
> The compromises only are needed if you embue "" with some meaning.
> Otherwise, life is good.
>
> ed

The problem is not, as others have repeatedly stated, if '' has any meaning. The problem is that the LENGTH('') should be 0 and not NULL. "Should" because it's (nearly) everywhere else except in oracle. The empty string has a length of 0.
NULL has a length of NULL.
In Oracle ("only") we have the stupid situation that the length of two concatenated strings can be different from the added length of the single strings.
Now tell me how that is good.

cheers,
Martin Received on Tue Aug 21 2007 - 01:57:17 CDT