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.

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

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

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.