Greetings from behind the firewall !

I've missed some of this thread by dint of being (a) behind a firewall,
(b) being away from home and (c) having very limited connectability, so
forgive me if I'm repeating what has been said before.

Oracle is ever so slightly non-ANSI standard in its handling of NULLs
and empty strings. To be blunt, Oracle considers an empty string to be
NULL and vice versa.

To this end, you can try this (best viewed with fixed pitch font):

SQL> create table norm (a varchar2(30), b char(30));
Table created.

SQL> insert into norm (a,b) values ('','');
1 row created.

SQL> insert into norm (a,b) values (null, null);
1 row created.

SQL> commit;
Commit complete.

SQL> select nvl(a, 'A is null') as a, nvl(b, 'b is null') as b
2 from norm;

A B
============================== ==============================
A is null b is null
A is null b is null

SQL> drop table norm cascade constraints;
Table dropped.


So you can see that empty strings and NULLs are identical, and
non-standard in Oracle. This is how Oracle has worked for many years
and many versions.

Not only that, but it has not changed at version 9i (release 1 or 2) nor
at version 10g - a NULL is still an empty syring and vice versa. One
day perhaps it might change ........ :o)


HTH

Cheers,
Norman (an Oracle DBA !)

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/