Re: Warning about null and open question to Oracle

John Jones <john_at_iluvatar.tip.duke.edu> wrote:
>First, a warning about null values. I knew that Null's were treated special
>in Oracle, but this problem is weird. I had a very LONG stored proc that
>was acting strange. We were comparing two tables to see if values where
>changing from one run to the next. The two tables were a like but the
>changes only got made to one table. In my proc I would open a cursor from
>one table and compare the values in the other table. If the values
>changed I would change them in the other table and then write the changes
>to a log file. The problem came when I noticed that I was updating almost
>every record every time, even when I ran the procedure when there were no
>changes to the first table. I spent 4 days working on this problem. I
>finally figured out that if both columns that were being compared were
>null then they were not equal. WHY????? It seems that null would be

                                 ^^^^^^^^^

Because of ANSI, specifically, ISO 9075 states

In short, if any value in a comparision { =, <, >, <>, <=, >= } is NULL then the return is UNKNOWN, not true, not false. Clause (vii) above, coupled with the requirement in a) above, do not permit A=B to return TRUE or FALSE if A and/or B is null.

To solve your problem, either use NVL or use IS NULL. For example:

if ( nvl( a, 0 ) = nvl( b, 0 ) ) then

   .....
end if;

OR

if ( a = b OR ( a is null and b is null ) ) then

    ...
end if;

Note that the first example, using the NVL function, may return erroneous results if 0 is a valid value for A or B.

The other solution is to not use NULLS in the database at all.

>equal to null????? To any Oracle Corp. people out there ... Will this
>ever be fixed???

If it ain't broke, don't fix it. No, this will not be 'fixed', it is in line with the standard. Other databases behave the same way. I know earlier versions of other databases did it differently, but they are and have changed their handling of NULLS to come in line with the standard.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Jun 13 1995 - 00:00:00 CEST