PL/SQL Record property conditions

Question

I have the following record

Cursor my_cursor Is Select y, x, z From table Order by y;
my_record my_cursor%rowtype;

Knowing that the record x property is null, am trying to check it against a value in a while loop and am getting the following results:

While my_record.x != 'TXT' Loop // loop does not enter
While NVL(my_record.x, '') != 'TXT' Loop // loop does not enter
While NVL(my_record.x, ' ') != 'TXT' Loop // loop enters

I was wondering why the first 2 tries are not entering in the loop knowing that when debugging, the return value is true of the expression?

The first one doesnt evaluate to true when `my_record.x IS NULL`. So is ok not enter. Other two should enter because `{'', ' '} <> 'TXT'`. But doesnt make sense `2)` doesnt enter and `3)` loop enter :/ — Juan Carlos Oropeza, Dec 07 '16 at 13:57
See this discussion: http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null — DCookie, Dec 07 '16 at 14:10

score 3 · Accepted Answer · answered Dec 07 '16 at 14:05

Short answer: because

'' is treated as NULL
Oracle uses Three-valued logic when it compares NULL with values

The condition NULL != 'TXT' is neither true nor false. It is unknown.

NVL(my_record.x, '') is NULL and therefore logical value of NVL(my_record.x, '') != 'TXT' is also unknown.

By the way - even NULL is not equal to NULL

You may want to read more from documentation

PL/SQL Record property conditions

1 Answers1