SQL> select * from test;
X
----------
qqq
111
www
222
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ------------
X VARCHAR2(10)
SQL> desc test2;
Name Null? Type
----------------------------------------- -------- -----------
TEST_ROWID ROWID
X VARCHAR2(10)
BEGIN
FOR i in (select rowid,x from test) LOOP
BEGIN
insert into test1
values(i.x);
exception
when INVALID_NUMBER
then
insert into test2
values(i.rowid,i.x);
DBMS_OUTPUT.PUT_LINE(i.rowid||'Conversion of string to number failed');
END;
END LOOP;
-- EXCEPTION WHEN OTHERS THEN
--
END;
======================
2nd method
======================
declare
y number(10);
BEGIN
FOR i in (select rowid,x from test) LOOP
BEGIN
y := to_number(i.x);
exception
when others
--when INVALID_NUMBER
then
--insert into test2
-- values(i.rowid,i.x);
DBMS_OUTPUT.PUT_LINE(i.rowid||i.x||' Conversion of string to number failed');
END;
END LOOP;
-- EXCEPTION WHEN OTHERS THEN
--
END;
====
declare
n number;
begin
for i in (select END_PAIR from xxx ) loop
begin
n:=to_number(i.END_PAIR);
exception
when others then
dbms_output.put_line(i.END_PAIR);
end;
end loop;
end;
