Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle NULL vs '' revisited

Re: Oracle NULL vs '' revisited

From: Brian Peasland <>
Date: Fri, 17 Aug 2007 13:33:22 -0500
Message-ID: <46c5dde7$0$32552$>

Thomas Kellerer wrote:
> Serge Rielau wrote on 17.08.2007 19:38:

>>> I would be interested: if this is important for character columns, 
>>> then I assume this is important for other columns as well.
>>> How do you implement that distinction for e.g. NUMBER or DATE columns?

>> If I get an assignment and I hand in an empty sheet that would be well 
>> empty. I typed zero characters.
>> If I don't hand it in it is missing. It is not decidable whether my 
>> work is good or bad, long short.. there is the NULL.

> If I don't hand it in, I don't "create a row in the database" that is
> something different compared to handing in an empty "row/assignment"

Depends on your data model doesn't it? What if the table looks like this:

Bob	      75       82
Tom           83
Kristi        94       92
Chick         56       47

So what does the entry for Tom in EXAM2 mean? There is a row in the table after all!

>> For numbers I think one can reasonably argue that 0 is the equivalent 
>> of empty.

> Hmm. I get an an assignment where I should calculate something. I hand
> in a sheet where I put 0 (zero) as the solution. So that is the same as
> handing in an assignment where I didn't enter anything.
> Doesn't sound logical to me.

I don't think that I'd ever equate 0 to empty for numbers. But maybe that's the mathematician in me. When computing averages or min values, a 0 can have a profound affect on the result where as no value can mean something else. Take for example the following set of numbers:

{0, 11, 22, 33, 44}

The average is 22 and the min is 0. Now take these numbers:

{NULL, 11, 22, 33, 44} Now the average is 27.5 and the min is 11.

So for those reasons, I would not reasonably argue that 0 is the equivalent of empty. Of does depend on how you are going to use this data. There are cases where it does not make any difference.

>> 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.
>> Question: If empty strings make no sense for VARCHAR, why have them 
>> for CLOBs? AFAIK Oracle has helper functions to produce empty lobs.

> I think that is a mere technical reason as for the CLOB some "management
> structures" (such as a locator) need to be created while this is not
> necessary with character data.




Brian Peasland

Remove the "nospam." from the email address to email me.

"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown

Posted via a free Usenet account from
Received on Fri Aug 17 2007 - 13:33:22 CDT

Original text of this message