Re: function to_number_or_null

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 07 Jul 1999 19:56:49 GMT
Message-ID: <3787aec4.24285290@inet16.us.oracle.com>

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;
  exception
    when others then
      testnumber := null;
  end;

  return testnumber;

end to_number_or_null;

hope this helps

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Jul 07 1999 - 14:56:49 CDT