NULL vs empty string.

From: Rich Mycroft <rich.mycroft_at_synchrologic.com>
Date: 2000/07/03
Message-ID: <y9085.4189$JZ4.11929@newsfeed.slurp.net>#1/1

Thought I'd pass on the results of our efforts to resolve the problem stated in the subject line. If one does something along the lines of nvl(myvar, -1)

we get a result of -1 as expected.

If we do

nvl('', -1)

we also get -1. The kind folks at Oracle support have now admitted that indeed the 8x engine can't tell the difference between the two values, release notes not withstanding. Remarkably enough Sybase ASE, MS SQL Server, Sybase ASA and other databases can tell the difference. This strikes me as a possible, pretty low level, failure to comply with the ANSI standards on NULLs on the part of Oracle, but then they all have areas that they each individually fail in - so what's the real difference?

Rich Mycroft
DB Master (If they have web masters then I should be a DB master.) Received on Mon Jul 03 2000 - 00:00:00 CDT