3 Replies Latest reply: Feb 25, 2013 7:38 AM by François Degrelle RSS

    comparing value against null variable

    789736
      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
          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
          • 3. Re: comparing value against null variable
            François Degrelle
            Also think of the NVL() function
            if nvl(v_variable,'x') = 'test' then
            Francois