Re: Isn't '' and NULL different???

manoj_usa_at_yahoo.com (Manoj) wrote in message news:<2fb90e4c.0107100005.729195c9_at_posting.google.com>...
> Oracle is handling inserts of '' (empty string) in a very strange way.
> For inserts its treating '' as NULL (so if a table t1 has field c1
> which is say "char(10) NOT NULL" and I try inserting '' into it then I
> get an error saying NULL not allowed). This is strange as in RDBMSs ''
> is not really NULL.
>
> Another strange thing is that in select statments '' and NULL are not
> treated as same. eg.
> create table t2(c1 char(10) NULL);
> insert into t2 values('');
> select * from t2 where c1 = '' --> this doesn't return any rows (this
> behavious is what would happen in other RDBMSs)!!
>
>
> The behavious of Oracle in insert for '' seems strange. What do you
> Oracle gurus feel about this? Is there any way in which I can change
> this behaviour to make it the same as other RDBMSs?
>
> Thanks,
> - Manoj

Oracle has announced they will change this behavior, which has been there *always*. I'm not sure whether they implemented this in 9i, as they have announced it for several years. I have a feeling though in many situations disaster will strike, as no one is really prepared for this.
That said, in any currently shipping release -whatever it is, you don't mention it', you simply can't change this behavior. Of course I could also say, given the marketshare of Oracle, other RDBMSs need to adapt to Oracle, :)

Regards,

Sybrand Bakker, Senior Oracle DBA Received on Tue Jul 10 2001 - 15:27:42 CEST