What happens to the base table transactions when procedure has some error?

Procedure A calls procedure B. A updates table t1, t2. B updates table t3. If some error happens to B what happens to the updates in all those tables? Whether they will rollback or commit? If commits how far it will commit?

plsqlgeek
Profile Answers by plsqlgeek Questions by plsqlgeek
Aug 16th, 2013
3
9718

Questions by plsqlgeek answers by plsqlgeek

PL/SQL

Answer

Showing Answers 1 - 3 of 3 Answers

vishal.bhadange
Profile Answers by vishal.bhadange

Aug 24th, 2013

CASE 1 (If you are not handling exception in Procedure B, none of the table gets updated)

Code
PROCEDURE A


  BEGIN 


    UPDATE TABLE t1;


    UPDATE TABLE t2;


    CALL PROCEDURE B;


  END;


  


  


  PROCEDURE B


  BEGIN 


    UPDATE TABLE t3;


  END;

CASE 2 (If you are handling exception in Procedure B, table t1 and t2 gets updated, But table t3 will not update)

Code
PROCEDURE A


  BEGIN 


    UPDATE TABLE t1;


    UPDATE TABLE t2;


    CALL PROCEDURE B;


  END;


  


  


  PROCEDURE B


  BEGIN 


    UPDATE TABLE t3;


  EXCEPTION


    WHEN OTHERS THEN


      NULL;


  END;