Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Checking for Numeric Values
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