Oracle - Nulls and Booleans

SET SERVEROUTPUT ON;
 
DECLARE 

   TrueBoolean BOOLEAN := TRUE; 
   FalseBoolean BOOLEAN := FALSE; 
   NullBoolean BOOLEAN; 

   BoolAnd BOOLEAN; 
   BoolOr BOOLEAN; 

BEGIN 

   DBMS_OUTPUT.ENABLE( 100000 ); 

   IF NullBoolean THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL" is TRUE' ); 
   ELSIF NOT NullBoolean THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"NULL" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolAnd := TrueBoolean AND NullBoolean; 
   IF BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"TRUE AND NULL" is TRUE' ); 
   ELSIF NOT BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"TRUE AND NULL" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"TRUE AND NULL" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolAnd := NullBoolean AND TrueBoolean; 
   IF BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND TRUE" is TRUE' ); 
   ELSIF NOT BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND TRUE" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND TRUE" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolOr := TrueBoolean OR NullBoolean; 
   IF BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"TRUE OR NULL" is TRUE' ); 
   ELSIF NOT BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"TRUE OR NULL" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"TRUE OR NULL" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolOr := NullBoolean OR TrueBoolean; 
   IF BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR TRUE" is TRUE' ); 
   ELSIF NOT BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR TRUE" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR TRUE" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolAnd := FalseBoolean AND NullBoolean; 
   IF BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"FALSE AND NULL" is TRUE' ); 
   ELSIF NOT BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"FALSE AND NULL" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"FALSE AND NULL" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolAnd := NullBoolean AND FalseBoolean; 
   IF BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND FALSE" is TRUE' ); 
   ELSIF NOT BoolAnd THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND FALSE" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"NULL AND FALSE" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolOr := FalseBoolean OR NullBoolean; 
   IF BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"FALSE OR NULL" is TRUE' ); 
   ELSIF NOT BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"FALSE OR NULL" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"FALSE OR NULL" is neither TRUE nor FALSE' ); 
   END IF; 

   BoolOr := NullBoolean OR FalseBoolean; 
   IF BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR FALSE" is TRUE' ); 
   ELSIF NOT BoolOr THEN 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR FALSE" is FALSE' ); 
   ELSE 
      DBMS_OUTPUT.PUT_LINE( '"NULL OR FALSE" is neither TRUE nor FALSE' ); 
   END IF; 
END; 
/ 

---

Results - Oracle 7.3.4

---

Statement processed.
"NULL" is neither TRUE nor FALSE
"TRUE AND NULL" is neither TRUE nor FALSE
"NULL AND TRUE" is neither TRUE nor FALSE
"TRUE OR NULL" is TRUE
"NULL OR TRUE" is TRUE
"FALSE AND NULL" is FALSE
"NULL AND FALSE" is FALSE
"FALSE OR NULL" is neither TRUE nor FALSE
"NULL OR FALSE" is neither TRUE nor FALSE

Home

Mail :  jasonvogel@no-spampoboxes.com (remove the "NO-SPAM") Forwarding Homepage :  http://www.poboxes.com/jasonvogel

Copyright © : 1997 - 2005