Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: function to_number_or_null
On Wed, 07 Jul 1999 19:30:50 GMT, richard.sullivan_at_wiesbaden.netsurf.de (Richard
Sullivan) wrote:
>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;
>/
My guess is that you are not passing in a format and nslparms, so the command you are running is similar to this...
1 select nvl( to_number( '12345', null, null ), -666 ) from dual SQL> / NVL(TO_NUMBER('12345',NULL,NULL),-666)
-666
If you give a null format to to_number, it seems to convert the character string using that format, NULL. Its probably better to write your function like...
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
if nlsparms is not null and
format is not null then testnumber := to_number(numberstring, format, nlsparms); elsif format is not null then testnumber := to_number(numberstring, format); else testnumber := to_number(numberstring);end if;
return testnumber;
end to_number_or_null;
hope this helps
chris.
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.