Re: PL/SQL cursor question

From: Daniel Clamage <dclamageNOSPAM_at_telerama.com>
Date: 7 Jul 1998 21:28:27 -0400
Message-ID: <01bda9e5$52f05910$6f29c9cd@saturn>

I assume you're running an implicit cursor, of the form: SELECT column_list
INTO variable_list_or_record_variable
FROM table_list
WHERE where_clause;

Implicit cursors are expected to return exactly 1 row. When no rows are found, it generates the NO_DATA_FOUND exception. This is expected and normal.

What you do is put the implicit cursor in its own block, with its own excpetion handler.
BEGIN
  SELECT * FROM dept WHERE deptno = n_deptno; EXCEPTION
  WHEN NO_DATA_FOUND THEN
    NULL; -- this is like a NOP
END; What this does is trap the exception and then passes control to the next statement following the block. Here's a slightly contrived example: DECLARE
  CURSOR get_all_emps IS -- cursor to get all emplyee data     SELECT * FROM SCOTT.emp;
  emp_rec get_all_emps%ROWTYPE; -- record variable   status NUMBER;
BEGIN -- main block
  OPEN get_all_emps; -- compute data set   LOOP
    FETCH get_all_emps INTO emp_rec; -- get 1 row     EXIT WHEN get_all_emps%NOTFOUND; -- break loop     BEGIN -- protect loop from implicit cursor errors

      SELECT * INTO dept_rec  -- look for 1 dept row
      FROM dept
      WHERE deptno = emp_rec.deptno;
    EXCEPTION  -- error getting 1 dept row
      WHEN OTHERS THEN
        status := SQLCODE;  -- always trap error code
        -- display error
        DBMS_OUTPUT.put_line('Employee ' || emp_rec.empno ||
                                             ' has a bad deptno: ' ||

emp_rec.deptno);

    END; -- embedded block
  END LOOP; -- getting employees
  CLOSE get_all_emps;

EXCEPTION -- main handler
  WHEN OTHERS THEN
    status := SQLCODE;
    IF (get_all_emps%ISOPEN) THEN
      CLOSE get_all_emps;
    END IF;
END;
/
If the implicit cursor fails for whatever reason, the problem is noted and the looping can continue til end-of-set. Had I not trapped it, the exception would have "bubbled up" to the next higher block, prematurely exiting the loop.

The outer exception block does cleanup by closing any potentially open explicit cursors. I STRONGLY recommend coding this sort of thing, to make code bullet proof.

Drop me a line if you need further clarification. --
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.

Iancrozier <iancrozier_at_aol.com> wrote in article <1998070722430600.SAA06361_at_ladder01.news.aol.com>...
> While running a PL/SQL program which uses a cursor, the program bombs at
a
> certain select statement with the following message:
> no rows selected.
> Well, this is true for some of the data. The question is :
> How can I stop the program from bombing just because no rows are selected
for
> this select statement.
> I would like to place a value in a variable when no rows are found,
instead
> of having the program abort.
>
> TIA
>
Received on Tue Jul 07 1998 - 20:28:27 CDT