Search Oracle Blogs

List of Blogs maintained by Paweł Barut.


Wednesday, February 07, 2007

Empty String IS NOT NULL?

Today friend of mine show me interesting case:
declare
  var varchar2(3);
  chr char(3);
begin
  var := ''; -- Empty string here
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NOT NULL
I must say that I was expecting that both variables will be null. It might be my lack of experience with CHAR datatype as I usually use VARCHAR2 even when it is expected that string will have constant length.
declare
  var varchar2(3);
  chr char(3);
begin
  var := null;
  chr := var;
  if var is null then
    dbms_output.put_line('VAR IS NULL');
  end if;
  if chr is null then
    dbms_output.put_line('CHR IS NULL');
  else
    dbms_output.put_line('CHR IS NOT NULL');
  end if;
end;
/
VAR IS NULL
CHR IS NULL
Just as expected. So it seems that in some cases NULL and empty string is not equivalent. But this difference can be only seen in PL/SQL. When I tried to reproduce this in pure SQL it was always NULL:
SQL> create table a(v1 varchar2(3), v2 varchar2(3), c1 char(3), c2 char(3));
Table created.
SQL> insert into a values (null, '', null, '');
1 row created.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL
SQL> update a set c1 = v1, c2 = v2;
1 row updated.
SQL> select dump(v1) v1,dump(v2) v2,dump(c1)c1,dump(c2) c2 from a;
V1    V2    C1    C2
----- ----- ----- -----
NULL  NULL  NULL  NULL


Cheers, Paweł

2 comments:

Noons said...

One of the "joys" of the inconsistent treatment of empty strings in Oracle, I guess...

Well spotted.

Sabarinathan Arthanari said...

Great. Thanks for sharing

 

Copyright © Paweł Barut
Printing from DOS to USB Printer