Xref: alice comp.databases.oracle.server:56407
Path: alice!news-feed.fnsi.net!WCG!newsfeed.berkeley.edu!newsfeed.stanford.edu!paloalto-snf1.gtei.net!news.gtei.net!inet16.us.oracle.com!not-for-mail
From: clbeck@us.oracle.com (Christopher Beck)
Newsgroups: comp.databases.oracle.server
Subject: Re: function to_number_or_null
Date: Wed, 07 Jul 1999 19:56:49 GMT
Organization: Oracle Corp.
Lines: 101
Message-ID: <3787aec4.24285290@inet16.us.oracle.com>
References: <3783a616.1767021@news.vistec.net>
Reply-To: clbeck@us.oracle.com
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Trace: inet16.us.oracle.com 931377442 16487 138.1.120.248 (7 Jul 1999 19:57:22 GMT)
X-Complaints-To: usenet@inet16.us.oracle.com
NNTP-Posting-Date: 7 Jul 1999 19:57:22 GMT
X-Newsreader: Forte Agent 1.5/32.451

On Wed, 07 Jul 1999 19:30:50 GMT, richard.sullivan@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@us.oracle.com
Reston, VA.
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation