NO_DATA_FOUND exception

How do I know which SELECT generated the NO_DATA_FOUND ?

Example:

CREATE [..]
DECLARE
--
BEGIN
  SELECT [...];   -- will this
  SELECT [...];   -- or this to generate NO_DATA_FOUND ?
EXEPCTION
WHEN NO_DATA_FOUND TH开发者_如何学运维EN
  [...]
END;

You could do this:

DECLARE
--
BEGIN
  BEGIN
    SELECT [...];   -- will this
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      [...]
  END;
  BEGIN
    SELECT [...];   -- or this to generate NO_DATA_FOUND ?
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      [...]
  END;
END;

Or:

DECLARE
  l_where_am_i number;
BEGIN
  l_where_am_i := 1;
  SELECT [...];   -- will this
  l_where_am_i := 2;
  SELECT [...];   -- or this to generate NO_DATA_FOUND ?
EXEPCTION
  WHEN NO_DATA_FOUND THEN
    CASE l_where_am_i
       WHEN 1 THEN [...]
       WHEN 2 THEN [...]
    END CASE;
END;

Another option is to use dbms_utility.format_error_backtrace as shown below (though it would be better to send that data to an error-logging routine). This function will give you the actual line number the error occurred on.

CREATE [..]
DECLARE
--
BEGIN
  SELECT [...];   -- will this
  SELECT [...];   -- or this to generate NO_DATA_FOUND ?
EXEPCTION
WHEN NO_DATA_FOUND THEN
  [...]
  dbms_output.put_line(dbms_utility.format_error_backtrace());
END;