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
: equal to null????? To any Oracle Corp. people out there ... Will this
: ever be fixed???

You are exactly correct, a NULL does not equal NULL. Null is always undefined. Since it has no definition it cannot be equal to another undefined value. This is how NULL are treated in ORACLE and I don't think it is something that needs to be fixed. One way to handle this is to wrap nvl(your_column_her,0) in your cursor select or in the comparison this will ensure that NULL will be handled identically.

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

   do something;
END IF; gthomas_at_us.oracle.com Received on Mon Jun 12 1995 - 00:00:00 CEST