Re: Q: pl/sql function question ?

Hi
  change when INVALID_NUMBER to when OTHERS

regards,
Cindy

tedchyn_at_yahoo.com wrote:

> 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;
> =============
> declare
> tf boolean;
> b_host varchar2(10);
> cursor cur1 is select b from t1;
> begin
> open cur1;
> loop
> fetch cur1 into b_host;
> exit when cur1%notfound;
> if not is_number(b_host) then
> dbms_output.put_line('The value is:'||b_host);
> end if;
> end loop;
> end;
> /
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Sun Sep 05 1999 - 16:23:50 CDT