udf question

[AlexB1234]

Don't do much Oracle development, but I think I've got a simple problem.

I'm trying to test whether the value in a varchar field is numeric with the following user defined function. The problem is I don't know how to trap for an Oracle error inside the function.

Anyone?


CREATE OR REPLACE FUNCTION IsNumber
(
psString VARCHAR2
)
RETURN NUMBER
IS
n NUMBER;
BEGIN
/* Need to trap for error 06502 - conversion error and return 0 if it occurs*/
n := TO_NUMBER(psString);
RETURN -1;
END;
/

 

[lewisp]

Try

CREATE OR REPLACE FUNCTION IsNumber (psString VARCHAR2)
RETURN NUMBER IS
  n  NUMBER;
BEGIN
  n := TO_NUMBER(psString);
  RETURN -1;
EXCEPTION
  WHEN OTHERS
  THEN
    Return 0;
END;