Re: Function fails to return boolean

This is not a bug, it is a feature.
Boolean is supported in pl/sql only, not in sql.

On Mon, 7 Feb 2000 08:51:31 -0800, "Kurt Johnson" <kujotx_at_hotmail.com> wrote:

>(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
>
>

Hth,

Sybrand Bakker, Oracle DBA Received on Tue Feb 08 2000 - 01:10:15 CST