Re: Oracle For Fun

"Tony" <andrewst_at_onetel.net.uk> wrote in message news:c0e3f26e.0402030234.2ba855b4_at_posting.google.com...
> "VC" <boston103_at_hotmail.com> wrote in message
news:<YBCTb.205971$I06.2284013_at_attbi_s01>...
> Well, to be pedantic, it doesn't say that NULL is not valid, it says
> its validity is unknown (which is correct for a NULL):

The previous poster said 'does not handle null'. If he said that the function returns NULL, I'd most certainly not argue with that.

>
> 1 declare
> 2 l_str varchar2(20);
> 3 b boolean;
> 4 begin
> 5 for l in (select x from t1) loop
> 6 b := f1(l.x);
> 7 if b then l_str := 'valid';
> 8 elsif not b then l_str := 'not valid';
> 9 else l_str := 'unknown'; end if;
> 10 dbms_output.put_line('<'||l.x||'>'||l_str);
> 11 end loop;
> 12* end;
> tandrews_at_IDEV
> SQL> /
> <123-456-7890>valid
> <123-456-78j0>not valid
> <>unknown
>
> PL/SQL procedure successfully completed.
>
> My version (translate3) with the NVL makes NULL invalid.

In my opinion, it's an unnecessary step since the function application as "if b then l_str := 'valid'; else l_str := 'invalid'; end if;' does the same.
Besides, what if one wanted to check if the column being checked does contain a NULL ? In your implmentation, you've lost this information by merging NULL and invalid.

>This could
> also be construed as correct, since in Oracle NULL and '' are
> indistinguishable, and '' is certainly not of the correct form.

Regarding 'correct', I disagree completetly: there is no logical connection between "since in Oracle NULL and '' are  indistinguishable" and "certainly not of the correct form". Hint, you have first to define the "correct form".

Rgds.

VC Received on Tue Feb 03 2004 - 05:46:23 CST