Re: Numeric string ??

From: Olivier BRUZEAUX <obruzeau_at_telecom.capgemini.fr>
Date: Thu, 9 Dec 1999 11:19:38 +0100
Message-ID: <82nvmi$a0$1@minus.oleane.net>

I want to do that in a loop, I tried this but without success:

BEGIN DELETE FROM OBR ;
DECLARE

my_abonum       filesms.abonum%TYPE;
tmp             number;
num             number;

CURSOR c1 IS select abonum from filesms where fildatenit = 2147483647 and abonu
m like '3360000%' ;

BEGIN DELETE FROM OBR ; OPEN c1;
LOOP
  BEGIN
        FETCH c1 INTO my_abonum ;

        SELECT to_number (abonum)
        INTO   tmp
        FROM   FILESMS
        WHERE ABONUM = my_abonum ;


  EXCEPTION
        WHEN INVALID_NUMBER THEN
                INSERT INTO obr VALUES (my_abonum) ;
        WHEN NO_DATA_FOUND THEN
                EXIT ;

  END ;
END LOOP ;
CLOSE C1;
END;
/

Kenneth C Stahl <BlueSax_at_Unforgettable.com> a écrit dans le message : 384E5E95.79FB2E6C_at_Unforgettable.com...
> 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.
Received on Thu Dec 09 1999 - 04:19:38 CST