Re: In a SQL request NULL and '' do not come up with the identical result

Could you cite where in the release notes? Reason I'm asking this is that I'm sitting at a prompt in SQLPLUS and if I enter the following :

SELECT nvl('', -1) from dual;

I get a return value of -1, pretty strongly inicating the thing thinks that '' equates to null. Let's play with this a little more. If I run the following query

SELECT nvl(NULL, -1) from dual;

I also get -1. Same as the first one. But if I enter the following

SELECT nvl('a', -1) from dual;

I get 'a', which one would expect. Now let's create a function (and this is where I get the real problem) as follows:

CREATE OR REPLACE FUNCTION CheckNull(strIn VARCHAR2) RETURN NUMBER
IS
retLen NUMBER;
BEGIN
    IF (strIn IS NULL) THEN

        retLen = -1;
    ELSE
        rtLen = nvl(length(strIn), 0);
    END IF;
    RETURN retLen;
END CheckNull;
/

One would expect that one performing the followng: select CheckNull(NULL) from dual;

to get -1, which it does.

select CheckNull('a') from dual;

returns 1.

Great! Now try this:

select CheckNull('') from dual;

and lo and behold we still get -1. This is with Oracle 8i on NT. So it looks very much to me as if Oracle is still treating '' as being equivalent to NULL, the release notes apparently not withstanding. So, is there some setting some where that needs to be tweaked? Sure would like to know about this. Is there some flag that can be set on the connection? Bet this is lurking somewhere in them thar notes, but please give a little more info other than just essentially RTFM. After all, if the only answer is RTFM what do we need this news group for? I've done RTFM and it's pretty massive, sitting as it does on a flaming CD, and after spending 8 hours working through their blasted search tool I'd really like to get an answer to this!

Rich Mycroft

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:961001272.25103.0.pluto.d4ee154e_at_news.demon.nl...
> Your answer is in the release notes.
> As from Oracle 8 an empty string is not equal to NULL anymore.
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> "Olivier Utkala" <olivier.utkala_at_msg-software.com> wrote in message
> news:8i8473$dld$1_at_s2.feed.news.oleane.net...
> > Someone knows the way to come up with the identical result for these
 two
> > requests to an Oracle 8.05 table with a nullable column :
> >
> > select * from table where col = '' -- this request gives back no reply
> > and
> > select * from table where col IS NULL -- this request gives a reply back
> >
> > Is there an option/parameter of the instance to alter in order to solve
 my
> > problem ? or any other suggestion ???
> >
> >
> > Thanking you in advance for your prompt reply !
> >
> > Olivier
> >
> >
>
>
Received on Fri Jun 16 2000 - 00:00:00 CDT