Cursor FOR LOOP- exception issues

Hi I am trying to use a Cursor FOR LOOP.
After opening cursor, i want to raise exception if data is not found. However, i am unable to do so as it jumps out of loop. However, even no exception is thrown. can anyone help me.


DECLARE
CURSOR dept_cur IS
SELECT department_id, department_name FROM department;
BEGIN
FOR dept_rec IN dept_cur LOOP
-- implicit open and implicit fetch occur
if dept_cur%NOTFOUND then
Raise_application_error( -20050, 'Error: No data found ' );
end if;
IF dept_rec.department_id = 30 THEN
DBMS_OUTPUT.PUT_LINE (TO_CHAR(dept_rec.department_id) ||
' ' || dept_rec.department_name);
END IF;
END LOOP; -- implicit close cursor
EXCEPTION
WHEN NO_DATA_FOUND THEN
err_msg := SUBSTR(SQLERRM,1,100);
err_cde := SQLCODE;
END;

Quote:

---

Originally Posted by newbie7

Hi I am trying to use a Cursor FOR LOOP.
After opening cursor, i want to raise exception if data is not found. However, i am unable to do so as it jumps out of loop. However, even no exception is thrown. can anyone help me.


DECLARE
CURSOR dept_cur IS
SELECT department_id, department_name FROM department;
BEGIN
FOR dept_rec IN dept_cur LOOP
-- implicit open and implicit fetch occur
if dept_cur%NOTFOUND then
Raise_application_error( -20050, 'Error: No data found ' );
end if;
IF dept_rec.department_id = 30 THEN
DBMS_OUTPUT.PUT_LINE (TO_CHAR(dept_rec.department_id) ||
' ' || dept_rec.department_name);
END IF;
END LOOP; -- implicit close cursor
EXCEPTION
WHEN NO_DATA_FOUND THEN
err_msg := SUBSTR(SQLERRM,1,100);
err_cde := SQLCODE;
END;

---

Looking at the code I think the cursor%NOTFOUND is intended to exit the loop when the cursor is empty, if this is the case I would rely on CURSOR%ROWCOUNT before enteriing the FOR LOOP.

No reason to get into FOR LOOP if the cursor is empty.