-
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;
************************************
-
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;
-
I am surprised to see the LOOP within the FOR.... I thought FOR was a looping construct already.
-
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;
/
-
You need the LOOP in FOR loops
Syntax for CURSOR FOR LOOP is:
FOR record_index IN cursor_name
LOOP
END LOOP;
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|