Hi,
i needed a function which I could call from the SQL*Loader which would
automatically set any numbers it couldn't interpret to NULL.
I wanted to catch the exception thrown by an invalid number
and return NULL.
To test it I tried returning obviously false numbers but each time
I get NULL returned no matter what input i provide.
Anyone know why ?
- FUNCTION TO_NUMBER_OR_NULL
- Returns: a NUMBER if the input was valid or NULL if it was not.
- Parameters: As for Oracle's TO_NUMBER.
- Description: Should do exactly the same as Oracle's TO_NUMBER but
just
- quietly swallows up data conversion errors and returns
- a NULL instead of complaining.
CREATE OR REPLACE FUNCTION TO_NUMBER_OR_NULL(numberstring IN VARCHAR2,
format IN VARCHAR2 := NULL,
nlsparms IN VARCHAR2 := NULL) RETURN NUMBER
AS
Testnumber NUMBER;
BEGIN
BEGIN
Testnumber := to_number(numberstring, format, nlsparms);
RETURN Testnumber; --never returns a number !
EXCEPTION
WHEN OTHERS THEN RETURN 999; -- never executes !
END;
RETURN 123; -- never executes !
END TO_NUMBER_OR_NULL;
/
Received on Wed Jul 07 1999 - 14:30:50 CDT
