Re: Oracle NULL vs '' revisited

On Aug 27, 9:05 am, "Martin T." <0xCDCDC..._at_gmx.at> wrote:
> 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

Nulls are a fact of life in SQL generally (despite being regarded as an abomination by some relational theorists) and are not unique to Oracle. PL/SQL has to follow SQL, as making it different would create even more complication and errors. The SQL Server/Sybase etc way does not eliminate nulls, it just gives you a second type of emptiness to code around.

I can appreciate that in a programming context you might code something like

v_middle_name = '';

and

if length(v_middle_name) = 0

or indeed

javascript:alert(''.length)

but while that might be fine for JavaScript (no disrespect btw - I like JavaScript) you can't get away with that in a database query for the kind of reasons we have been discussing in this thread. Received on Mon Aug 27 2007 - 04:00:47 CDT