Re: CHR(0) ver null
- From: Tony van Lingen <tony.vanlingen@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 08 Aug 2007 11:30:49 +1000
Interesting, but it works out of the box on my DB:
SQL> select value from v$parameter where name = 'compatible';
10.1.0.4.0
SQL> select 1 from dual where dummy >= chr(0);
1
----------
1
Logically I would have thought that chr(0) represents ASCII 0, which is
the 1st character from the ASCII set, whereas the empty string does not
contain any characters?
Cheers,
Tony
Phil Singer wrote:
> 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...
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
--
Tony van Lingen
Systems Administrator and DBA
___________________________
Disclaimer
WARNING: This e-mail (including any attachments) has originated from a
Queensland Government department and may contain information that is
confidential, private, or covered by legal professional privilege, and may be
protected by copyright.
You may use this e-mail only if you are the person(s) it was intended to be
sent to and if you use it in an authorised way. No one is allowed to use,
review, alter, transmit, disclose, distribute, print or copy this e-mail
without appropriate authority. If you have received this e-mail in error,
please inform the sender immediately by phone or e-mail and delete this e-mail,
including any copies, from your computer system network and destroy any
hardcopies.
Unless otherwise stated, this e-mail represents the views of the sender and not
the views of the Environmental Protection Agency.
Although this e-mail has been checked for the presence of computer viruses, the
Environmental Protection Agency provides no warranty that all viruses have been
detected and cleaned. Any use of this e-mail could harm your computer system.
It is your responsibility to ensure that this e-mail does not contain and is
not affected by computer viruses, defects or interference by third parties or
replication problems (including incompatibility with your computer system).
E-mails sent to and from the Environmental Protection Agency will be
electronically stored, managed and may be audited, in accordance with the law
and Queensland Government Information Standards (IS31, IS38, IS40, IS41 and
IS42) to the extent they are consistent with the law.
___________________________
--
//www.freelists.org/webpage/oracle-l
Other related posts:
