Re: Oracle NULL vs '' revisited

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 28 Aug 2007 16:17:07 +0200
Message-ID: <46d42e6e$0$230$e4fe514c@news.xs4all.nl>

"Martin T." <0xCDCDCDCD_at_gmx.at> schreef in bericht news:46d2aeba$0$1343$834e42db_at_reader.greatnowhere.com...

> William Robertson wrote:

>> 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.

>>
>
> Of course NULLs are a fact. But I think a column with NOT NULL is often 
> acceptable.
>
> While we are at Java*, lets assume this very-pseudo code here:
> ---
> String str = "hello";
> int len = 0;
> str.substr(0, len);
> JDBC.insert("MY_VARCHAR_COL", str);
>
> ...
>
> String strx = JDBC.select("MY_VARCHAR_COLUMN");
> // And now, because it's oracle, we do NOT get a String object with len 0, 
> but we get a null. So we have to program around orcacle's varchar
> if(strx == null) {
>   strx = new string();
> }
> ---
>
> Since most of the languages used to get data out of Oracle make the 
> distinction of NULL/null/undefined vs. '' so should oracle. It would make 
> working with VARCHAR2 columns easier I think.
>
> br,
> Martin

Shakespeare has left the building.......

Sorry guys, threads are getting this long the tree fills up my screen...

Shakespeare
(What's in a null?) Received on Tue Aug 28 2007 - 09:17:07 CDT