DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Cursor FOR LOOP- exception issues

  1. #1
    Join Date
    Feb 2012
    Posts
    1

    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;

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Quote Originally Posted by newbie7 View Post
    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.
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width