Re: Numeric string ??

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Dec 1999 08:50:57 -0500
Message-ID: <k0os4s0gf0gara1dlej687sg434dbmps1v@4ax.com>

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 Received on Wed Dec 08 1999 - 07:50:57 CST