Programming Oracle® Triggers and Stored Procedures, Third Edition

11.4. Expressions with NULL

The combination of a NULL with an AND in a predicate will not give you the results you expect. A variable that is NULL has no value. Therefore, you cannot compare it to anything else with meaningful results. An expression of comparison with a NULL does not evaluate. When such an expression exists within an IF statement and there is an ELSE part, the code does not evaluate to TRUE, so it follows the ELSE path. From this, it can appear that the condition evaluates to FALSE. If you then reverse the logic in the IF test with a NOT operator, the same ELSE path is taken.

Consider two variables: OTHER_NAME and MY_NAME. A NULL initializes the variable OTHER_NAME.

 my_name VARCHAR2(100) := 'SCOTT'; other_name VARCHAR2(10) := ...