Re: Oracle NULL vs '' revisited

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 online  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 Received on Mon Aug 20 2007 - 21:25:53 CDT