This is not a bug, this is design decision taken by Oracle long time ago and
it is unlikely to change. You have to live with it - empty varchar2 in Oracle
was NULL, is NULL and probably will be NULL for the life of Oracle database
software.
--
Vladimir Zakharychev (bob@dpsp-yes.com) http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.
"Wolfram Roesler" <wr_at_grp.de> wrote in message news:Xns927174CE4539Fwrgrpde_at_62.153.159.134...
> Hello,
>
> does anyone know why Oracle stores empty VARCHAR2 strings as NULL?
> That is, (assuming the table has exactly one row,)
>
> UPDATE table SET colname='';
> SELECT * FROM table WHERE colname='';
> (nothing found)
> SELECT * FROM table WHERE colname IS NULL;
> (row is found)
>
> In my understanding of the logic behind NULL, it should be a value
> distinct from an empty string. Is this a bug in Oracle? If so, when
> will it be fixed? Do I have to use NVL(colname,'') all over to
> prevent the fix from breaking my code?
>
> I found a thread on Google where this was discussed in 1999 (search
> for subject "differences between null and empty varchar2"); does
> anyone have more up-to-date information?
>
> Thanks for your help
> W. Roesler
Received on Wed Aug 21 2002 - 04:46:30 CDT
