I am looping over a cursor and fetching row by row.
If an excetion is thrown during the fetch, i want to catch it, do some logging and continue with fetching the next row:
procedure check_table(p_table_name varchar2) is
cur ref_cur;
stmt varchar2(4000);
dummy_rec dummy_rec_type; -- always fits for the result of the select
begin
-- build select statement dynamicly
stmt := build_control_sql(p_table_name);
-- stmt is something like 'select to_date(x,'yyyymmdd'), ... from table_x'
if stmt is not null then
open cur for stmt;
loop
begin
fetch cur
into dummy_rec;
exit when cur%notfound;
exception
when others then
-- there are problems with this row, do some logging and continue with next row
write_log('error');
end;
end loop;
close cur;
end if;
end;
But at the next fetch after the first exception handling, a ORA-01002 "fetch out of sequence" is thrown.
Seems like the cursor is no longer valid after the first exception.
Is there any way to handle fetch exceptions and continue with fetching the next row?