Other Null Examples

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

Advertisement