Xref: alice comp.databases.oracle.server:56400
From: richard.sullivan@wiesbaden.netsurf.de (Richard Sullivan)
Newsgroups: comp.databases.oracle.server
Subject: function to_number_or_null
Date: Wed, 07 Jul 1999 19:30:50 GMT
Message-ID: <3783a616.1767021@news.vistec.net>
X-Newsreader: Forte Free Agent 1.1/32.230
Path: alice!news-feed.fnsi.net!news.idt.net!dispose.news.demon.net!demon!newsfeed.nacamar.de!newsfeed.nacamar.de!news-hh.maz.net!news-fra.maz.net!juno.wiesbaden.netsurf.de!poneu010.wiesbaden.netsurf.de
Lines: 40

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;
/