Charles
SQL> select a from t1 where a not in (select b from t2 where b is not null and c ='');
no rows selected
SQL> select a from t1 where a not in (select b from t2 where b is not null and c =' ');
A
----------
1
Controversially, Oracle treats the empty string '' as NULL
$ sqlplus xxxxxxxx
SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 10 16:04:55 2007
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production
SQL> select count(*) from dual where '' = ''
2 /
COUNT(*)
----------
0
(and I get the same behaviour on XE 10.2.0.1)
So when you say WHERE C = '' you have a predicate that can never be true (because if you set C to '', it is actually null).
I don't see why cursor sharing would be relevant (sorry, can't read the Metalink note, as the Support ID here is a top secret not available to actual Oracle cognoscenti...)
Cheers Nigel
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Jan 10 2007 - 10:13:37 CST
