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

Home -> Community -> Usenet -> c.d.o.server -> Re: Is This the varchar2 ZLS Issue?

Re: Is This the varchar2 ZLS Issue?

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 20 Mar 2006 21:16:06 +0100
Message-ID: <dvn1p2$mon$1@news4.zwoll1.ov.home.nl>


Tim Marshall wrote:
> Arto Viitanen wrote:
>

>> Tony Andrews wrote:
>>
>>> All correct - for Oracle.  But note that the ANSI standard is that the
>>> empty string is NOT null, and "where '' = ''" should always be TRUE.
>>> This Oracle anomaly is a trap for those used to other DBMSs that honour
>>> the ANSI standard on this point.
>>
>> I used to think this also. But honestly, where do you use value ''?

>
> First of all, thank you to Mark for the response and all for the
> discussion.
>
> Perhaps my original example shows a possibility. The entity/table in
> question is a work order/request table. A row represents a request or
> order for maintenance related work to be performed by tradespeople.
> Depending on the nature of the request, the cost of the work may have to
> be recovered from the client. If so, a debit account is entered in the
> wo_acct_no column or left blank. The designers that developed this
> particular DB have the value where wo_acct_no is blank as null.
>
> I know from discussions I've read on other groups that many of the folks
> who are stringent about relational theory proscribe to never leaving a
> column value as null. What would one do for a default value, then, for
> a varchar2 where information can be left out?
>

NULL.
That means: undefined, no information (which -freely interpreted- is your "where information can be left out").

As Oracle treats '' (empty string) as NULL, you will not win anything, using '' over NULL.
It will create misunderstanding, as Oracle's handling of empty strings is an anomaly. But that is not your doing, that's Oracles doing.

BTW: Why should a column never be NULL? A tuple may well be undefined at a certain time (e.g. number of children while status='SINGLE' - although some people... but that is entirely OT), but become meaningful at a later stadium.

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Received on Mon Mar 20 2006 - 14:16:06 CST

Original text of this message

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