Q: pl/sql function question ?

From: <tedchyn_at_yahoo.com>
Date: Sun, 05 Sep 1999 19:39:06 GMT
Message-ID: <7qugsp$bkv$1@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;

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 - 14:39:06 CDT