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 NULLI 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 NULLJust 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