Re: stupid newbbie question

The only stupid question is the one you didn't ask because you were afraid.

You can also use explicit cursors to avoid leaping to the nearest exception handler.
declare
  cursor dept_c(Cdeptno dept.deptno%type) is     select *
    from dept
    where deptno = Cdeptno;
  dept_rec dept_c%rowtype;
begin
  open dept_c(10);
  fetch dept_c into dept_rec;
  if (dept_c%notfound) then -- do your no_data_found error handling here     ...
  end if;
  close dept_c;
end;

You will find that using implicit cursors (select into) for single-row lookups is faster than the above method. Be sure to encapsulate an implicit cursor in its own block. This is especially true for DML statements. open some_cursor;
loop
  fetch some_cursor into some_rec;
  exit when some_cursor%notfound;
  begin
    update some_other_table
    set some_column = some_rec.some_similar_column     where some_key = some_rec.some_similar_key;   exception
  when others then
    null; -- do your exception handling here   end;
  ...
end loop;
This localizes any exceptions to a suitable spot within the loop. Processing the data set can continue unhindered by the error when trying to update.

--
- Dan Clamage
http://www.telerama.com/~dclamage
If you haven't crashed the Server,
you haven't been trying hard enough.
Kent Eilers wrote in message <366DA824.3777493E_at_pca.state.mn.us>...


>I writing some PL/SQL procedures and functions and have come up against

>a problem whenever a query returns no rows (ala the ORA-01403: no data


>found error message).  I've looked in three books to get a strategy on

>dealing with this and have come up with zilch.

>

>Basically I do not want to go to an exception handler when this

>happens.  I thought I had escaped this drainhole by first running a

>query to dump the count(*) into a local variable.  But this still

>triggers the 1403 error.

>

>What do all you PL/SQL pro's do?  My (rather limited) understanding of

>the exception handler is you cannot 'on error resume....' so how do deal

>with this?

>

>Any response greatly appreciated!

>

>