Re: Numeric string ??

Thomas Kyte wrote:
>
> A copy of this was sent to Kenneth C Stahl <BlueSax_at_Unforgettable.com>
> (if that email address didn't require changing)
> On Wed, 08 Dec 1999 08:35:17 -0500, you wrote:
>
> >Olivier BRUZEAUX wrote:
> >>
> >> I am looking for an sql command to test in my string is only numeric ??
> >> VARCHAR string [15]
> >>
> >> Olivier
> >
> >If you are talking about plsql, then do it like this:
> >
> >Function Is_Number(Buff varchar2) return integer is
> > Dummy Number := 0;
> >begin
> > Dummy := Buff;
> > return(0);
> >exception
> > when invalid_number then
> > return(1);
> > when others then
> > return(2)
> >end;
> >
> >If you pass a numeric string to the function it will return 0 if the value
> >was truely numeric, if it is an invalid number it will return 1. If there
> >was some other error it will return 2.
>
> No, the above will return 0 or 2 and never 1.
>
> INVALID_NUMBER is raised by SQL doing an explicit conversion from a string to a
> number. VALUE_ERROR is raised by plsql doing an explicit or implicit conversion
> OR sql doing an implicit conversion.
>
> Consider:
>
> tkyte_at_8.0>
> 1 declare
> 2 x number;
> 3
> 3 procedure p(s in varchar2) is begin dbms_output.put_line(s);end;
> 4 begin
> 5
> 5 begin
> 6 p( 'IMPLICIT CONVERSION x := ''a''' );
> 7 x := 'a';
> 8 p( 'No exception' );
> 9 exception
> 10 when value_error then p( 'Value Error' );
> 11 when invalid_number then p( 'Invalid Number' );
> 12 end;
> 13
> 13 begin
> 14 p( 'EXPLICIT CONVERSION x := to_number(''a'')' );
> 15 x := to_number( 'a' );
> 16 p( 'No exception' );
> 17 exception
> 18 when value_error then p( 'Value Error' );
> 19 when invalid_number then p( 'Invalid Number' );
> 20 end;
> 21
> 21
> 21 begin
> 22 p( 'EXPLICIT CONVERSION select to_number(''a'') ' ||
> 23 'into x from dual;' );
> 24 select to_number('a') into x from dual;
> 25 p( 'No exception' );
> 26 exception
> 27 when value_error then p( 'Value Error' );
> 28 when invalid_number then p( 'Invalid Number' );
> 29 end;
> 30
> 30 begin
> 31 p( 'IMPLICIT CONVERSION select ''a'' into x from dual;' );
> 32 select 'a' into x from dual;
> 33 p( 'No exception' );
> 34 exception
> 35 when value_error then p( 'Value Error' );
> 36 when invalid_number then p( 'Invalid Number' );
> 37 end;
> 38 end;
> 39 /
> IMPLICIT CONVERSION x := 'a'
> Value Error
> EXPLICIT CONVERSION x := to_number('a')
> Value Error
> EXPLICIT CONVERSION select to_number('a') into x from dual;
> Invalid Number
> IMPLICIT CONVERSION select 'a' into x from dual;
> Value Error
>
> PL/SQL procedure successfully completed.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle Corporation

had the concept right - just the wrong exception.

................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................
................................................................................