This discussion is archived
3 Replies Latest reply: Feb 25, 2013 5:38 AM by FrançoisDegrelle RSS

comparing value against null variable

789736 Explorer
Currently Being Moderated
Hi guys, quick question. Just want to see if my understanding of the code below is correct:

Declare
v_variable varchar2(100)
Begin
v_variable := null;

if v_variable = 'test' then
message('test');
end if;

if v_variable != 'test' then
message('test2');
end if;

If my understanding is correct message 'test2' should print but 'test' wont or is it possible I could get an error if we cant compare against null values?

Thanks

Thanks.




End;
  • 1. Re: comparing value against null variable
    Christian Erlinger Guru
    Currently Being Moderated
    You have to compare null values with is null. A comparision of null with = or != always evaluates to NULL and thus you are not reaching your outputs:
    $[CHE_TEST@asterix1_impl] r
      1  begin
      2  if null = null then
      3  dbms_output.put_line('result: true');
      4  elsif null != null then
      5  dbms_output.put_line('result: false');
      6  else
      7  dbms_output.put_line('result: else');
      8  end if;
      9* end;
    result: else
    Both if conditions evaluate to NULL, so the else branch is executed.

    null = null acutally evaluates to null as you can see:
    $[CHE_TEST@asterix1_impl] r
      1  declare
      2    bres boolean;
      3  begin
      4    bres := null = null;
      5    if bRes is null then
      6      dbms_output.put_line('Result: NULL');
      7    else
      8      dbms_output.put_line('Result: NOT NULL');
      9    end if;
     10* end;
    Result: NULL
    see
    http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/if_statement.htm#LNPLS01324

    cheers
  • 2. Re: comparing value against null variable
    789736 Explorer
    Currently Being Moderated
    Thanks :)
  • 3. Re: comparing value against null variable
    FrançoisDegrelle Oracle ACE
    Currently Being Moderated
    Also think of the NVL() function
    if nvl(v_variable,'x') = 'test' then
    Francois

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points