Re: CHR(0) ver null

Wojciech Skrzynecki wrote:
> Hello
>
> What component in DB I have to install to use ">= CHR(0)" instead of
> "is not null" .
>

Set the compatibility to 7.0

This is a lloooonnnnngggggg standing point with Oracle: The empty string is used to represent a NULL. Which introduces a problem: the predicate

      (1) COL1 = NULL is supposed to be malformed and illegal. Of course, it is easy for the syntax checker to detect the above, but what about the following:

       (2) COL = '' (where I have written two single quotes with no space in between them).

This construction is a back door into the sort of syntax which is supposed to be illegal, and which Oracle generally does not allow. Instead, it will treat expressions like '' as if one had coded NULL instead, and evaluate them accordingly. And, no matter how hard you try to store an empty string in a VARCHAR2 column, it will be treated and handled as if you had stored a null instead. (including CHR(0)).

Now, back in release 6.x and 7.0, this was not enforced so strictly, and expressions like (1) _could_ be used. I remember bringing in the latest release of Oracle to my shop (7.1.34), then taking a new job 8 months later at a 7.0.9 shop, and finding expressions like (1) all over the place. I was mystified as to why they worked. My boss was mystified as to why they started failing when we upgraded to 7.1...

--
http://www.freelists.org/webpage/oracle-l