Re: Select problem

stephen O'D wrote:
> 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.

Nothing strange about it. This is documented Oracle behavior that has not changed since the first commercial RDBMS was released.

What is strange is that you did all of this rather than just reading the concepts docs or Tom Kyte's "Expert one-on-one Oracle" or any of numerous sources. Hopefully this is a gentle nudge in that direction as likely there is much else you've not discovered such as multiversion read consistency.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)