Compare numbers in plsql with greatest und least


Plsql is a nice and simple programming langunage for oracle databases, but sometimes you can’t believe what you see. In the following example you can see the Schrödinger’s cat equivalent of oracle.

  grNumber number;
  leNumber number;
  testNumber number := 10;
  grNumber := greatest (null,testNumber);
  leNumber := least (null,testNumber);
  if grNumber = testNumber then
    dbms_output.put_line(testNumber || ' is greater then null');
    dbms_output.put_line('null is greater then ' || testNumber);
  end if;

  if leNumber = testNumber then
    dbms_output.put_line(testNumber || ' is less then null');
    dbms_output.put_line('null is less then ' || testNumber);
  end if;

The result is:

null is greater then 10
null is less then 10

In this case null is greater and less then any number.

To avoid this strange behavior you have to use nvl to avoid null in greatest und least functions.