Re: numeric check

From: Ubiquitous <weberm_at_polaris.net>
Date: Mon, 24 Apr 2006 13:49:14 -0400
Message-ID: <WeKdnZKTivoHktDZRVn-rQ@giganews.com>

chrisval_at_gmail.com wrote:
>Malcolm Dew-Jones wrote:

>> -- untested
>> function is_a_number( s varchar2)
>> return number
>> is
>> begin
>> return to_number(s);
>> exception
>> -- This should check just the expected error
>> -- but I haven't bothered to look it up.
>> when others then
>> return NULL;
> end is_a_number;
>
>What you're looking for is: "INVALID_NUMBER"

I thinkthis throws a VALUE_ERROR exception. What's the difference?

Not to be pedantic, but shouldn't this function return a BOOLEAN instead of a NUMBER, so you have something like:

FUNCTION is_numeric(s VARCHAR2)

   RETURN BOOLEAN
IS

   return_value BOOLEAN := FALSE ;
   v_numeric NUMBER := 0;
BEGIN
   v_numeric := TO_NUMBER(s);
   return_value := TRUE ;
   RETURN return_value;
EXCEPTION
   WHEN VALUE_ERROR THEN
      RETURN return_value;
   WHEN OTHERS THEN

      DBMS_OUTPUT.put_line('Unexpected EXCEPTION!');
      RETURN return_value;

END is_numeric; Received on Mon Apr 24 2006 - 12:49:14 CDT