CODEWITHSUNDEEP
oracle-databaseexceptionplsql
eztam
eztam

Reputation: 3829

Are following statements executed in any case if the first one throws an exception?

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

Upvotes: 0

Views: 53

Answers (2)

Namandeep_Kaur
Namandeep_Kaur

Reputation: 378

Once the control goes to an exception block, it doesn't go back to the begin or declare section of the pl/sql block. Following the same, if there is an error in your first select statement, the exception block will be executed and the respective handler would be used. In case, you have neither mentioned the respective handler nor a WHEN OTHERS, the control will go to the calling environment (either any procedure or interface/ IDE).

In case, you still wish to run the second select statement, you could write another pl/sql block in exception handler.

Upvotes: 1

alexgibbs
alexgibbs

Reputation: 2480

In PLSQL, the occurrence of an exception stops program exectuion at the point of the exception and jumps to the EXCEPTION block, if any, for handling, else raises the exception to the client.

You can see this behavior in a test block.
Here we will print before and after the exception, and can observe the next statement after the exception does not get printed:

  BEGIN
    DBMS_OUTPUT.PUT_LINE('About to throw an exception');
    RAISE_APPLICATION_ERROR(-20001,'Exception');
    DBMS_OUTPUT.PUT_LINE('Done throwing exception');
  EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Handling the exception');
END;
/

Result:

About to throw an exception
Handling the exception

Note: in the example you provided, the EXCEPTION block only handles NO DATA FOUND exceptions, so other types of exceptions will be raised instead of running through the exception handler. But in any case, things will stop processing at the point of the exception.

Upvotes: 3

Related Questions