Re: stupid newbbie question

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Tue, 08 Dec 1998 23:19:01 GMT
Message-ID: <3673acb2.34533696@inet16.us.oracle.com>

On Tue, 08 Dec 1998 16:28:00 -0600, Kent Eilers <kent.eilers_at_pca.state.mn.us> wrote:

>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?

You can 'wrap' begin/exception/end blocks around any amount of code within a procedure. It does not have to be at the procedure level itself. You can also imbed a begin/exception/end block within another begin/exception/end block.

eg.

create or replace
procedure my_proc as
  l_var1 number := -1;
  l_var2 number := -1;
begin
  begin
    select col1

      into l_var1
      from foo
     where col2 = 100;
    begin
      select col1
        into l_var2
       from bar
      where col2 = l_var1;
    exception
      when NO_DATA_FOUND then
        l_var2 := -2;  -- do something

    end;
  exception
    when NO_DATA_FOUND then
      null; -- do nothing, ( ie. 'on error resume' )   end;
  --

  dbms_output.put_line( l_var1 );
  dbms_output.put_line( l_var2 );
end my_proc;

/

This could also be written with no exception blocks at all

create or replace
procedure my_proc as
  l_var1 number := -1;
  l_var2 number := -1;
begin
  for x in ( select col1 from foo where col2 = 100 ) loop     l_var1 := x.col1;
    l_var2 := -2;
    for y in ( select col1 from bar where col2 = l_var1 ) loop       l_var2 := y.col1;
    end loop;
  end loop;

  dbms_output.put_line( l_var1 );
  dbms_output.put_line( l_var2 );
end my_proc;

/

Your choice.

Both these examples assume that col2 is a unique key in both tables. Otherwise you could get TOO_MANY_ROWS exception in the first case and different results from the second procedure every time you run it.

hope this helps.

chris.

>
>Any response greatly appreciated!

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 08 1998 - 17:19:01 CST