Other Null Examples February 3, 2009

If we are playing with NULLs, we have to be careful. Let's see it with two examples.

 

 

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
 
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PROCEDURE isapple(p_apple IN VARCHAR2) IS
  2       v_nullval VARCHAR2(50);
  3  BEGIN
  4       IF p_apple <> 'APPLE'
  5       THEN
  6            dbms_output.put_line('NOT APPLE');
  7       ELSE
  8            dbms_output.put_line('APPLE');
  9       END IF;
 10  END isapple;
 11  /
 
Procedure created
SQL> begin
  2    isApple(p_apple => NULL);
  3  end;
  4  /
 
APPLE
 
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PROCEDURE isapple_sln(p_apple IN VARCHAR2) IS
  2  BEGIN
  3       --solution 1
  4       IF nvl(p_apple, 'X') <> 'APPLE'
  5       THEN
  6            dbms_output.put_line('Sol1: NOT APPLE');
  7       ELSE
  8            dbms_output.put_line('Sol1: APPLE');
  9       END IF;
 10  END isapple_sln;
 11  /
 
Procedure created
SQL> begin
  2    isApple_sln(p_apple => NULL);
  3  end;
  4  /
 
Sol1: NOT APPLE
 
PL/SQL procedure successfully completed

--

Another example; In the "Understand the implications of NULL" section of http://www.psoug.org/reference/null.html-

--

Did you learn the lesson? :p

  What will the be the output when we run the following PL/SQL block?

DECLARE

     v_name VARCHAR2(100);

     v_id   NUMBER;

BEGIN

     IF v_name = v_id THEN

          dbms_output.put_line(‘Equal’);

     ELSIF v_name = NULL THEN

          dbms_output.put_line(‘v_name is NULL’);

     ELSIF v_name IS NULL OR v_id IS NULL THEN

          dbms_output.put_line(‘One of the variables is NULL’);

     ELSE

          dbms_output.put_line(‘NOT Equal’);

     END IF;

END;

 

A)      Equal

B)       One of the variables is NULL

C)       NOT Equal

D)      An VALUE_ERROR exception raises

E)     v_name is NULL