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.
CURSOR dept_cur IS
SELECT department_id, department_name FROM department;
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 ' );
IF dept_rec.department_id = 30 THEN
DBMS_OUTPUT.PUT_LINE (TO_CHAR(dept_rec.department_id) ||
' ' || dept_rec.department_name);
END LOOP; -- implicit close cursor
WHEN NO_DATA_FOUND THEN
err_msg := SUBSTR(SQLERRM,1,100);
err_cde := SQLCODE;
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.
Originally Posted by newbie7
No reason to get into FOR LOOP if the cursor is empty.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width