Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    2

    Unanswered: 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 THROW exception.


    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
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  4. #4
    Join Date
    Feb 2012
    Posts
    2

    Thanks a lot

    I love your explaination and how systematic it is. Thanks for taking time and explaining beautifully.
    I know, I was trying to avoid that Open/Close cursor bcos of so much typing. But I guess thats the only way.

    SO i need to raise exception to let user know that the table he is hitting/querrying for doesnt have any data. If i dont, its shown sucessful. any ideas to get around.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    declare
    cnt number;
    begin
    cnt := 0;
    for pnt in (select 1 one from dual where 1=2) loop
    cnt := cnt + 1;
    end loop;
    if cnt = 0 then
    raise_application_error(-20001,'NOTHING FOUND');
    END IF;
    END;
    /
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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