Cursor FOR loop is smart. It won't raise NO-DATA-FOUND as it is; if there's nothing to be fetched, it will exit the loop and terminate execution successfully. Therefore, if you meant to handle it as an EXCEPTION - you can not.
Here's an example: cursor FOR loop, although there's nothing to be fetched, won't raise an exception:
Code:
SQL> declare
2 cursor cur_r is select dname from dept where 1 = 2;
3 begin
4 for c1 in cur_r loop
5 dbms_output.put_line(c1.dname);
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
On the other hand, the same SELECT statement will raise NO-DATA-FOUND:
Code:
SQL> declare
2 l_dname dept.dname%type;
3 begin
4 select dname
5 into l_dname
6 from dept
7 where 1 = 2;
8 end;
9 /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4
SQL>
This is your cursor FOR loop example (adjusted to my Scott's schema) - it does the job just fine:
Code:
SQL> DECLARE
2 CURSOR dept_cur IS
3 SELECT deptno, dname from dept;
4 dept_rec dept_cur%rowtype;
5 BEGIN
6 for dept_rec in dept_cur loop
7 if dept_cur%notfound then
8 Raise_application_error( -20050, 'Nothing to fetch any more - exiting the loop');
9 exit;
10 end if;
11
12 IF dept_rec.deptno = 30 THEN
13 DBMS_OUTPUT.PUT_LINE (TO_CHAR(dept_rec.deptno) || ' ' || dept_rec.dname);
14 END IF;
15 end loop;
16 end;
17 /
30 SALES
PL/SQL procedure successfully completed.
If you wanted to catch an exception, go with another approach - a lot of more typing for you, more things to take care of (declare a cursor, a cursor variable, open a cursor, fetch from it, handle situation when there's nothing to fetch any more, close a cursor) (that's why cursor FOR loops are much more convenient - they do most of stuff for you - see the following example):
Code:
SQL> begin
2 for dept_rec in (select deptno, dname from dept) loop
3 if dept_rec.deptno = 30 then
4 dbms_output.put_line (to_char(dept_rec.deptno) || ' ' || dept_rec.dname);
5 end if;
6 end loop;
7 end;
8 /
30 SALES
PL/SQL procedure successfully completed.
And, finally, catching that %NOTFOUND by yourself:
Code:
SQL> declare
2 cursor dept_cur is
3 select deptno, dname from dept;
4 dept_rec dept_cur%rowtype;
5 begin
6 open dept_cur;
7 loop
8 fetch dept_cur into dept_rec;
9 if dept_cur%notfound then
10 raise_application_error( -20050, 'Nothing to fetch any more - exiting the loop');
11 exit;
12 end if;
13
14 if dept_rec.deptno = 30 then
15 dbms_output.put_line (to_char(dept_rec.deptno) || ' ' || dept_rec.dname);
16 end if;
17 end loop;
18 close dept_cur;
19 end;
20 /
30 SALES
declare
*
ERROR at line 1:
ORA-20050: Nothing to fetch any more - exiting the loop
ORA-06512: at line 10
SQL>
The question is - why would you want to do that at all? Previous 8-lines example is all you need to do, simple and clear.
dBforums
>
Cursor FOR LOOP- exception issues
02-03-12, 17:29
But I guess thats the only way.
Linear Mode
