Re: Oracle For Fun

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Mon, 02 Feb 2004 21:15:27 -0800
Message-ID: <1075785267.863977@yasure>

VC wrote:

> Hello Daniel,
>
> "Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1075776599.3474_at_yasure...
>

>>VC wrote:
>>
>>>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?

>
>
> I expect it to insert a null into the 'x' column and Oracle somehow lives
> up to my expectations:
>
> SQL> drop table t1;
>
> Table dropped.
>
> SQL> create table t1(x varchar2(20));
>
> Table created.
>
> SQL> insert into t1 values('123-456-7890');
>
> 1 row created.
>
> SQL> insert into t1 values('123-456-78j0');
>
> 1 row created.
>
> SQL> insert into t1 values(null);
>
> 1 row created.
>
> SQL>
> SQL> select count(*) from t1;
>
> COUNT(*)
> ----------
> 3
>
> SQL>
>
>

>>Try select count(*) from t1;

>
>
> I just did ;)
>
>

>>You need to create another column ...

>
>
> I don't -- pls. see above.
>
>
> Just wondering -- what did _you_ expect Oracle to do ?
>
> Rgds.
>
> VC
>
>

Nothing now that I take a second to reread what you did. Damn these Percodans are making a mess of my brain.

-- 
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 - 23:15:27 CST