Home » SQL & PL/SQL » SQL & PL/SQL » Are following statements executed in any case if the first one throws an exception?
Are following statements executed in any case if the first one throws an exception? [message #673456] Tue, 20 November 2018 07:28 Go to next message
katherineamt
Messages: 1
Registered: November 2018
Junior Member
When an exception happens at the first select statement. Will the second select statement and the function then be executed in any case? Or will all following statements be skipped ?

BEGIN
   SELECT ...
   SELECT ...
   procedure_that_performs_select();
EXCEPTION
   WHEN NO_DATA_FOUND THEN  ...
END

Thanks
Katherine
Re: Are following statements executed in any case if the first one throws an exception? [message #673458 is a reply to message #673456] Tue, 20 November 2018 07:43 Go to previous message
cookiemonster
Messages: 13642
Registered: September 2008
Location: Rainy Manchester
Senior Member
Whenever an oracle error occurs code execution jumps immediately to the first exception handler in the call stack that can handle the error (if the error is too_many_rows then EXCEPTION WHEN NO_DATA_FOUND will be ignored).
If there is no handler in the current code block then it'll look in the block that contains/called the current block. If there isn't one there then it'll keep going up the levels till it finds one or ends up back at the client that submitted the DB code in the first place.

So no - if the first select errors then the 2nd select and the procedure won't run.
If that's not what you want then you need to either:
1) wrap the 1st select in a BEGIN/END with an exception handler:
BEGIN
  BEGIN

    SELECT ...
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
    <whatever>
  END;

  SELECT ...
  procedure_that_performs_select();

EXCEPTION
   WHEN NO_DATA_FOUND THEN  ...
END;

2) Move the 1st select to separate procedure/function that has it's own exception handler.
Previous Topic: calculate a function on whole table
Next Topic: How to display the letter starting with "B" followed by number only.
Goto Forum:
  


Current Time: Sun Jul 21 02:17:16 CDT 2019