Re: Select problem

In my experience Oracle behaves strangely with empty strings, especially in PL/SQL eg:-
SQL> @test.sql
SQL> declare
  2 v_empty_string varchar2(10) default '';   3 v_null_string varchar2(10) default null;   4 begin
  5 dbms_output.put_line ('v_empty_string is'||v_empty_string||'end');
  6 if v_empty_string is null then
  7 dbms_output.put_line ('v_empty_string conpares against null as true');
  8 end if;
  9
 10 if v_empty_string = '' then
 11 dbms_output.put_line ('v_empty_string compares against '''' as true');
 12 end if;
 13
 14 if v_null_string is null then
 15 dbms_output.put_line ('v_null_string conpares against null as true');
 16 end if;
 17
 18 if v_null_string = '' then
 19 dbms_output.put_line ('v_null_string compares against '''' as true');
 20 end if;
 21 end;
 22 /

v_empty_string isend
v_empty_string conpares against null as true
v_null_string conpares against null as true

PL/SQL procedure successfully completed.

SQL> Now maby is it something todo with quotequote tuning into a single quote, but i think line 5 above disproves that. Also consider:-

SQL> select count(*) from (select '' c1 from dual) where c1 is null;

  COUNT(*)

         1

SQL> select count(*) from (select ' ' c1 from dual) where c1 is null;

  COUNT(*)

         0

SQL> select count(*) from (select null c1 from dual) where c1 is null;

  COUNT(*)

         1

SQL> Interesting ... empty string is treated as a Null ... I worked this quirk out sometime ago after much painful debugging!

I concluded from this that empty string and null in Oracle are one and the same (at least as far as PLSQL), which is very different that in just about every other language. Received on Tue Mar 01 2005 - 15:18:47 CST