Re: empty string == NULL

Despite Celko's statement, a NULL ( at least in Oracle) is NOT an unknown, it is a NULL - it may have some strange properties ( it is not larger or smaller than anything, for example), but it is still a value and can be tested for...A true unknown would be, for instance,in a Pl/Sql block that contains a declared but not assigned variable - the value of that variable, prior to assignment, is indeterminate and could be anything, depending on the previous contents of the memory location allocated to the new variable.

It would be good programming technique( in my humble opinion ) to avoid allowing blanks to be entered into a database column but to default to NULL if no value is supplied -

Just my 2c
John Greco
Oracle DBA/Application Developer

jerrygitomer_at_my-deja.com wrote:

>In article <3867B012.E5B995FB_at_dced.state.ak.us>,
> Calvin Crumrine
><Calvin_Crumrine_at_dced.state.ak.us> wrote:
>> I don't understand the statement that there's no
>way of knowing what an empty column contains.
>> It seems obvious to me that it contains nothing,
>although that leads to the English-statement
>> paradox that nothing is something. (The paradox
>may exist in other languages also, but it's
>> still only a paradox due to language
>limitations. Does 0 represent a quantity? Is black
>a
>> color? I won't say that all paradoxes are due to
>language limitations, but I suspect that most
>> of them are.)
>
>Sorry for my poor explanation before. Let me try
>again. The problem with NULL is, to quote Joe
>Celko in SQL for Smarties', "A NULL means that we
>have a missing, unknown, miscellaneous, or
>inapplicable value in the data."
>
>If a column can have a value ranging from -100 to
>+100 and no value has been specified it is
>presumptuos and, depending on the application,
>could even be life threatening to assume a value
>of 0. Ridiculous extreme example -- picture a
>submarine with an emergency lifesaving system that
>opened the escape hatch when the reading for the
>pressure gauge is zero, but the gauge is
>malfunctioning and the programmer arbitrarily
>decided to use the value 0. The lifesaving system
>kicks in and the sub is hundreds of feet below the
>surface -- goodbye submarine and crew.
>
>What do we do if the user has specified a real
>number where the column validation rules call for
>an integer value? Truncate, round, round up,
>substitue a zero, substitute the minimum value or
>maximum value allowed...
>
>
>> Likewise, rows containing nulls in columns being
>used to qualify candidates for selection
>> should be treated the same as any other value,
>i.e. returning FALSE or TRUE based on whether or
>> not you want to select rows containing nulls in
>those columns. This, as I understand it, is
>> exactly how nulls are in fact handled, i.e.
>simply as a separate and unique 'value'. It seems
>> to me that the primary advantage of Null is that
>it is typeless. That is, I can
>>
>> SELECT * FROM TABLEA WHERE COLUMNA = '' only
>when COLUMNA is a character datatype but I can
>> SELECT * FROM TABLEA WHERE COLUMNA IS NULL
>regardless of COLUMNA's datatype.
>>
>> Since these statements are not synonymous per
>Codd, it complicates the programming but it's
>> really no different than ensuring that COLUMNA
>is truncated when the user inserts nothing but
>> spaces. (Imagine the problem selecting character
>data if you don't truncate spaces! It's only
>> one extra step to check the length after
>truncating the spaces and change the 'value' to
>NULL
>> when the length=0. That preserves the logic if
>Oracle should ever change how it handles
>> 0-length strings. Sorry about calling NULL a
>value, but I can't think what else to call it.
>> Non-value?)
>>
>> Jerry Gitomer wrote:
>> >
>> > The "trinary" logic of having True, False, and
>Null goes back to Codd's
>> > original article on relational databases. I
>suspect that the only way it will
>> > get changed is if it is explicitly banned in
>the ANSI SQL standard.
>> >
>> > If I remember Codd rationale for NULL was that
>there is no way of knowing what
>> > an empty column contains and therefore rows
>containing nulls in columns being
>> > used to qualify candidates for selection
>should be ignored.
>> >
>> > You can always use the NVL function to convert
>NULL into whatever you feel is
>> > appropriate -- and the fact that any two
>developers might not agree as to what
>> > is appropriate tends to prove that Codd's
>theoretical approach is really quite
>> > practical
>> >
>> > hth
>> >
>> > Andreas Steidle wrote:
>> >
>> > > From the oracle doc:
>> > >
>> > > Do not use null to represent a value of
>zero, because they are not
>> > > equivalent. (Oracle currently treats a
>character value with a length of zero
>> > > as null. ... [1])
>> > >
>> > > Is there a workround for this contardictory
>semantik garbage? A string is a
>> > > string, even if it's empty!!! Perhaps some
>env-var??
>> > > In other words: Is there a way to make
>Oracle remember a '' and NOT convert
>> > > it to NULL ???
>> > >
>> > > thanx, Andreas
>> > >
>> > > [1] However, this may not continue to be
>true in future releases, and Oracle
>> > > recommends that you do not treat empty
>strings the same as NULLs.
>> > >
>> > > Hope this will happen very soon as default
>behavoir.
>
> --
> Once I figured out how to spell DBA I became
>one
> -- Jerry Gitomer
>
>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

  -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------    http://www.newsfeeds.com The Largest Usenet Servers in the World! ------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==----- Received on Wed Dec 29 1999 - 09:12:26 CST