Software Engineer Thoughts

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ł

Noons said...

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

Well spotted.

08 February, 2007 03:19

Sabarinathan Arthanari said...

Great. Thanks for sharing

11 August, 2010 11:24