Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Counterquestion - is Oracle a He or a She - or an It or a hermaphrodite

Re: Counterquestion - is Oracle a He or a She - or an It or a hermaphrodite

From: <rjsearle_at_gmail.com>
Date: Tue, 2 Aug 2005 18:29:10 +1000
Message-ID: <392977e5050802012990a5725@mail.gmail.com>


On 8/2/05, Billy Verreynne (JW) <VerreyB_at_telkom.co.za> wrote:

>
> Mark W. Farnham [mailto:mwf_at_rsiz.com] wrote:
>
> > Quite simply, and the most easily understood example I know, some
> people
> > have no middle name.
>
> Which means a non-existant middle name. No value. In programming terms
> that is called a null.

 Please allow me to split hairs...
 If the field "middle_name" contains a zero length string, then I would conclude that we *know* that this person does not have a middle name. This of course is subject to the meta-data rules that we design in to the information model for this enterprise, such as "Full name = CONCATENATE("first_name", " ", "middle_name", " ", "last_name")  BUT
 if the same field contains NULL then that can represent other things, such as "I do not know what the person's middle name is or even if the person has a middle name" This is different to the assertion that "I know the person has no middle name"
 While the resultant string might be the same, with careful use of the NULL value we can in fact derive other useful information. It really does depend on the rules of the business and what information it wishes to record. These discussions show that it is important for the the meaning of NULL be explicitly specified and understood by all who interact with the database.  NULL is a very contentious topic. I'm not surprised to see it highlight a diverse range of opinions. Perhaps this is another good reason to avoid it's use where possible.  

 > In that case, the value of the string of that person's middle name
> is not
> > NULL, but rather the accurate value for the person's middle name is
> the empty
> > string.
>
> Disagree!
>
> > The value is absolutely known, whereas the NULL value is definitely
> not known.
>
> The mistake is the inconsistent way people want to deal with the
> string data type.
>
> What values does this data type encompass? Characters. Now suddenly no
> character as in "an empty string" is also a character and a valid data
> type value? Come on!
> How do I render a value from a variable where that value is considered
> okay.. except there's no friggen value to render!!

 see the example above..
 Using the concatenate concept above, the system must define what "string+NULL+string" means. I'm sure that each reader will have a clear, unambiguous answer for thsi question. The problem is that they won't all agree.

An empty string is not the same as a number that is equal to zero.
> Zero is a value. An empty string has *NO* values. Not a single
> character. By definition when a variable has no value, it is null. It
> does not contain anything from the data type it encompasses. So how
> can a variable that does not contain any of its data type
> bytes/characters contain a value?

 If a string of length zero is equal to NULL then is the number zero the equal to NULL? My opinion is that zero and NULL are two different values (I expect there will be little debate on that). Zero is an absolute value, NULL is not. It is an abscence of a value. In the same way I feel that a string of length zero is also an absolute value. It is less obvious because the number zero has a visible representation (0) whereas an empty string (by definition) does not, hence this discussion.

> I don't know a better way to explain it than that.
>
> Well Mark, I in turn cannot understand why people do not seem to grasp
> the very fundemental concept of what a null is and what a value is. I
> do agree that dealing with NULLs in Oracle using state operators is
> not ideal as its easier to deal with NULLs using math operators (and
> in most other languages). But I do not agree with the misconception
> that an empty string is somehow different from a NULL string.

 This is a great thread, keep the ideas flowing

--

> Billy
Russell ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> This e-mail and its contents are subject to the Telkom SA Limited
> e-mail legal notice available at
> http://www.telkom.co.za/TelkomEMailLegalNotice.PDF
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> --
> http://www.freelists.org/webpage/oracle-l
> -- http://www.freelists.org/webpage/oracle-l
Received on Tue Aug 02 2005 - 03:31:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US