Function fails to return boolean

(I am testing this using SQL Navigator, but SQL*Plus also yielded the same result.)

My goal was to write a simple function to detect if a field is numeric and return a boolean from within a view (I thought there was an Oracle function already, but the "Complete Reference" didn't yielded one). But, I can't get my function to work in a SQL statement. If I run it in a script window with DECLARE...BEGIN... END and simply test for a value ("is_numeric(2);", or "is_numeric('NOT NUMERIC);"), it works with no error.

To make it work in a query, I currently have to return a varchar2 with 'TRUE' or 'FALSE', in order to get results. If I switch it to boolean, I get a datatype error (ORA-06553: PLS-382: expression is of worng type).

What in my code is preventing me from returning a boolean?

It busts when I do this:

FUNCTION IS_NUMERIC (parmNUMBER IN VARCHAR2)   RETURN BOOLEAN IS v_NUMBER NUMBER(9);

BEGIN
    v_NUMBER := TO_NUMBER(parmNUMBER);
    RETURN TRUE; EXCEPTION
   WHEN OTHERS THEN
       RETURN FALSE;
END; Here's my code for when it works (by returning a VARCHAR2):

FUNCTION IS_NUMERIC (parmNUMBER IN VARCHAR2)   RETURN VARCHAR2 IS v_NUMBER NUMBER(9);

BEGIN
    v_NUMBER := TO_NUMBER(parmNUMBER);
    RETURN 'TRUE'; EXCEPTION
   WHEN OTHERS THEN
       RETURN 'FALSE';
END; Thanks for any assistance,

Kurt Received on Mon Feb 07 2000 - 10:51:31 CST