Re: Oracle For Fun

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 Received on Mon Feb 02 2004 - 21:12:48 CST