Re: Numeric string ??

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 10:52:59 -0500, you wrote:

>Thomas Kyte wrote:
>>

[snip]

>> >
>> >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;

[snip]

>> 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.
>>
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
>had the concept right - just the wrong exception.
>

sure but "simple experimentation", as you put it, with your function would have shown the error right away :)

Anyway -- why do all of your posts have hundres of dots at the end? Makes every post 2x bigger then they should be.

>
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>................................................................................
>v

--
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 Received on Wed Dec 08 1999 - 12:11:39 CST