Stop PL/SQL code after exception (PL/SQL, ORACLE)

Question

I have check procedure:

PROCEDURE checkVariables                               
IS
   r_Var1    exception;
   r_Var2    exception;
BEGIN

 If g_Name is null then     
        RAISE r_Var1;
 End if;
 If g_Prefix is null then     
        RAISE r_Var2;
 End if;

 DBMS_OUTPUT.PUT_LINE('Variables Set Up');

 EXCEPTION
        When r_Var1 then
           DBMS_OUTPUT.PUT_LINE('Missing g_Name');               
        When r_Var2 then
           DBMS_OUTPUT.PUT_LINE('Missing g_Prefix');            
END;

If the exception is raised I want beside message also STOP/BREAK all other PL/SQL code (procedure procedure 3 and 4 will be not executed).

like:

execute procedure1
execute procedure2
execute checkVariables --raised exception, STOP/BREAK next code                               
execute procedure3
execute procedure4

How can I do that?

Wrap them in another procedure, change exception response as a variable out from proc, raise exception on outer proc based on variable — JohnHC
– JohnHC, Commented Jan 12, 2018 at 11:14

Kaushik Nayak · Accepted Answer · 2018-01-12 15:25:50Z

1

You could re-raise the exception from your checkVariables procedure. Run all your procedures inside a BEGIN..END with an EXCEPTION block

...
EXCEPTION
  WHEN r_var1 THEN
             DBMS_OUTPUT.PUT_LINE('Missing g_Name');
             RAISE; 

  WHEN r_var2 THEN
             DBMS_OUTPUT.PUT_LINE('Missing g_Prefix');
             RAISE;
END;
...

BEGIN

  procedure1;
  procedure2;
  checkVariables; --raised exception, STOP/BREAK next code                               
  procedure3;
  procedure4; 

EXCEPTION

WHEN  OTHERS THEN 

  DBMS_OUTPUT.PUT_LINE('EXCEPTION  OCCURED');
  DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE()); --Gives additional information

END;
/

Output would be something like this.

PROC1
PROC2
Missing g_Name
EXCEPTION  OCCURED
ORA-06512: at "HR.CHECKVARIABLES", line 21
ORA-06512: at "HR.CHECKVARIABLES", line 10
ORA-06512: at line 5

edited Jan 12, 2018 at 15:25

answered Jan 12, 2018 at 15:12

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

4est Over a year ago

thanks, I have also added ROLLBACK: when it's failed and when it success

Collectives™ on Stack Overflow

Stop PL/SQL code after exception (PL/SQL, ORACLE)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related