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:
One of the "joys" of the inconsistent treatment of empty strings in Oracle, I guess...
Well spotted.
Great. Thanks for sharing
Post a Comment