Cursor FOR loop vs. open/fetch/loop

[welchdor]

I would like to replace the following open/fetch/close statements with a cursor FOR loop, but I need to check for this particular DB2 error on every fetch, and I don't know how to do that error checking inside the FOR loop. Any help is appreciated.
*****************************************
cursor c1 is select ..........
e_DB2_error EXCEPTION;
PRAGMA EXCEPTION_INIT (e_DB2_error, -9100);
begin
open c1 cursor;
begin
fetch c1 into .....local variables;
exception
WHEN e_DB2_error THEN
etc. etc.
end;
while c1%found
loop
/* process rows... */
/* fetch the next row of the cursor */
begin
fetch c1 into .....local variables;
exception
WHEN e_DB2_error THEN
etc. etc.
end;
end loop;
close c1 cursor;
end;
************************************

[akkerend]

Use PL/SQL block with exception handler for your -9100 error inside the loop:
DECLARE
CURSOR C1 IS ...
EXCEPTION ...
PRAGMA ...
BEGIN
FOR r1 IN c1
LOOP
BEGIN
...
EXCEPTION
WHEN e_DB2_error THEN
...
END;
END LOOP;
END;

[welchdor]

I am surprised to see the LOOP within the FOR.... I thought FOR was a looping construct already.

[kmesser]

create or replace procedure myproc as
cursor c1 is select a from b;
e_DB2_error EXCEPTION;
PRAGMA EXCEPTION_INIT (e_DB2_error, -9100);
x varchar2(100);
begin
open c1;
begin
fetch c1 into x;
exception
WHEN e_DB2_error THEN
dbms_output.put_line('edb2error before loop');
end;
while c1%found loop
begin
fetch c1 into x;
exception
WHEN e_DB2_error THEN
dbms_output.put_line('db2error in loop');
end;
end loop;
close c1;
end;
/

[akkerend]

You need the LOOP in FOR loops

Syntax for CURSOR FOR LOOP is:

FOR record_index IN cursor_name
LOOP
<executable statement>
END LOOP;