Re: pl/sql function question ?

<tedchyn_at_yahoo.com> wrote in message news:7qugsp$bkv$1_at_nnrp1.deja.com...
> Sir, following error messages were received when I tried to execute
> following procedure:
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error
> ORA-06512: at "SCOTT.IS_NUMBER", line 5
> ORA-06512: at line 10
>
> Qestion:
> 1. why false is not returned by is_number function ? procedure stop
> when first invalid_number is encoutered ?
> 2. how do i get around this if i want to continue processing ?
>
> Thanks in advance Ted Chyn
> =======
> CREATE OR REPLACE FUNCTION is_number (str IN VARCHAR2)
> RETURN BOOLEAN
> IS
> v_num NUMBER;
> BEGIN v_num := TO_NUMBER (str);
> RETURN TRUE;
> EXCEPTION
> WHEN INVALID_NUMBER
> THEN RETURN FALSE;
> END;
In PL/SQL, you should use VALUE_ERROR instead of INVALID_NUMBER:

    WHEN VALUE_ERROR THEN INVALID_NUMBER is raised in SQL. Received on Tue Sep 07 1999 - 12:44:04 CDT