How to compare null value in PLSQL

Question

Below is my code.

One variable is NOT having value. Another variable is having a value. In the below code, I want to print that the variables are not same even in the case when var1 is not having any value. How can I do that?

CREATE OR REPLACE PACKAGE BODY mypackagebody IS

 PROCEDURE comparenull() IS
  l_var1 mytable.mycolumn1%TYPE;
  l_var2 mytable.mycolumn2%TYPE;

 BEGIN
  BEGIN
   SELECT var1
         ,var2
   INTO   l_var1
         ,l_var2
   FROM   mytable;
  EXCEPTION
   WHEN no_data_found THEN
    var1 := NULL;
    var2 := NULL;
  END;

  /* At this point var1 is NOT having any value and var2 is having a value.*/
  /* The below if condition is returing false. But, I wanted to go inside the if condition and print that the var values are not same*/
  IF var1 <> var2
  THEN
   dbms_ouput.put_line('var1 and var2 are not same');

  END IF;

 END comparenull;

END mypackagebody;

score 8 · Accepted Answer · answered May 20 '19 at 01:40

8

I think you want a NULL-safe comparison. In Oracle, you can use multiple conditions:

IF var1 <> var2 OR
   (var1 is null and var2 is not null) OR
   (var1 is not null and var2 is null)

answered May 20 '19 at 01:40

Gordon Linoff

1,242,037
58
646
786

For anyone else wondering why this makes any sense at all, it's important to realize that PLSQL uses trinary logic (true/false/undefined), and if one of the values is null, `var1 <> var2` evaluates to "at least one of the values is missing, so I dunno". – Theodore Murdock Nov 22 '22 at 02:59

score 5 · Answer 2 · answered May 20 '19 at 01:55

Probably the most sucinct way of handling this is by using NVL

if NVL(var1, 'NULL') <> NVL(var2, 'NULL')

NVL will evaluate the variable and if it is null use the string in the comparison instead this string can be whatever you want it doesn't have to be the literal string NULL I just find this useful.

How to compare null value in PLSQL

2 Answers2