Re: stupid newbie question

From: MarkP28665 <markp28665_at_aol.com>
Date: 8 Dec 1998 23:34:47 GMT
Message-ID: <19981208183447.08094.00001957@ng-cr1.aol.com>

From: Kent Eilers >>
 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 << removed excess >> What do all you PL/SQL pro's do? <<

When an exception occurs control passes to the exception handler for the affected block if one exists and then to the calling routine. To continue to process within a loop enclose the SQL statement within its own begin - end block with its own exception handler:

example:
begin
.. other code ...
open c_my_cursor;
loop
  fetch c_my_cursor into .......
  exit when c_my_cursor%notfound;
  begin
    select column_name

        into  v_variable
        from  some_table
        where table_col  = v_cursor_variable;

  exception
    when no_data_found then

         null; -- OK
   end;
end loop;
close c_my_cursor;
  {an outside exception handler could go here to handle other code} end;

I hope this helps, but be advised I typed this on the fly and typos are likely.

Mark Powell -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own advice - Received on Tue Dec 08 1998 - 17:34:47 CST