Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 02 Feb 2004 18:50:54 -0800
Message-ID: <1075776599.3474@yasure>

VC wrote:

> Hello Jeff,
>
> "Jeff" <jeff_at_work.com> wrote in message
> news:bvlojh$mti$1_at_cronkite.cc.uga.edu...
>

>>In article <1075682270.753428_at_yasure>, Daniel Morgan

>
> <damorgan_at_x.washington.edu> wrote:
>

>>>The contestants and results can be found at:
>>>http://www.psoug.org/sql_fun.html
>>>
>>>One lesson comes through very clearly from the results ... test, test,
>>>test. Don't be too quick to think you know what's best just by looking
>>>at the code you've written.
>>
>>Now, here I thought the object was to be as concise as possible.
>>
>>Of the four solutions, I think translate3 comes closest to winning the

>
> "most
>

>>elegant" award.  Translate2 should be disqualified because it doesn't

>
> handle
>

>>null values...

>
>
>
> Care to elaborate on "doesn' handle null values ? Just being curious...
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.4.0 - Production
>
> SQL> create or replace function f1( p_number in varchar2 ) return boolean is
> 2 begin
> 3 return translate(p_number,'0123456789','9999999999') =
> '999-999-9999';
> 4 end;
> 5 /
>
> Function created.
>
> SQL> create table t1(x varchar2(20))
> 2 /
>
> Table created.
>
> SQL> insert into t1 values('123-456-7890')
> 2 /
>
> 1 row created.
>
> SQL> insert into t1 values('123-456-78j0')
> 2 /
>
> 1 row created.
>
> SQL> insert into t1 values(null)
> 2 /
>
> 1 row created.
>
> SQL> set serverout on
> SQL> declare
> 2 l_str varchar2(20);
> 3 begin
> 4 for l in (select x from t1) loop
> 5 if f1(l.x) then l_str := 'valid'; else l_str := 'not valid'; end
> if;
> 6 dbms_output.put_line('<'||l.x||'>'||l_str);
> 7 end loop;
> 8 end;
> 9
> 10 /
> <123-456-7890>valid
> <123-456-78j0>not valid
> <>not valid <-- null is not valid
>
> PL/SQL procedure successfully completed.
>
> SQL>
>
>
> ????????????
>
>
> VC

What, exactly, do you expect "insert into t1 values(null)" to do?

Try select count(*) from t1;

You need to create another column ... for example

create table t1(seqno int, x varchar2(20));
insert into t1 values (1, '123-456-7890');
insert into t1 values (2, '123-456-78j0');

insert into t1 values (3, NULL);

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Mon Feb 02 2004 - 20:50:54 CST