Akdora’s Blog

Programming, Oracle, Life, Fun

Other Null Examples February 3, 2009

Filed under: Oracle — Akdora @ 6:36 am
Tags: , , , , , ,

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

 

One Response to “Other Null Examples”

  1. I go for “dbms_output.put_line(‘one OF THE variables IS null’);” since NULL can not be equal or not equal to anything, even to NULL with Oracle. These behaviours may change with other database vendors of course.

    Here is my related post for further NULL stories if anyone is interested: Chp 10- NULL values and Oracle – http://tonguc.wordpress.com/2007/01/13/oracle-concepts-and-architecture-part-3/


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s