Re: Oracle NULL vs '' revisited

From: Martin T. <0xCDCDCDCD_at_gmx.at>
Date: Mon, 27 Aug 2007 12:58:19 +0200
Message-ID: <46d2aeba$0$1343$834e42db@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

Received on Mon Aug 27 2007 - 05:58:19 CDT