Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Checking for Numeric Values

Re: Checking for Numeric Values

From: Heggelund <d92hegge_at_ix_prod.hfk.mil.no>
Date: 1997/03/21
Message-ID: <1997Mar21.115736.18842@ix_prod.hfk.mil.no>#1/1

Hi
the best way to do this is to use overloading in plsql.

In a package create one function for each datatype with the same name:

function is_numeric (field number) return boolean; function is_numeric (field varchar2) returne boolean; etc..

In the function bodies you return true for the one with the number parameter and false for the rest (this might not be syntactic correct but you get the picture):

function is_numeric (field number) return boolean begin
return true;
end;

function is_numeric (field varchar2) return boolean begin
return false;
end;

In your plsql code you then can write: if is_numeric(field) then ... and Oracle will pick the right function based on the current datatype of field. By the way field may also be a constant ,ie is_numeric('123');

Rgds
Steinar Heggelund

Steven Moyano (stevem_at_nwu.edu) wrote:

: I am working with data that sometimes is entirely numeric, sometimes
: alphanumeric (300,350A,400F, for example).
 

: Is there a way to imitate the IsNumeric function in MS Basic, which returns a
: Yes/No depending on whether the value can be converted to a number?
 

: TO_NUMBER(350A) yields ORA-01722: invalid number, and the program stops.
 

: Thanks very much for your time and help.

: Steven Moyano
: Northwestern University, Evanston, IL. USA
: stevem_at_nwu.edu

--

----------------------------------------------------------------------------
I'm employed in the Norwegian consulting company Opus One AS. 
I have 7 years experience with Oracle products, mainly the database. 
We are a small company which offers consulting services in design,
implementation and tuning of databases and applications based on Oracle.
Received on Fri Mar 21 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US