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?

Questions by plsqlgeek   answers by plsqlgeek

Showing Answers 1 - 1 of 1 Answers

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

Code
  1. PROCEDURE A

  2.   BEGIN

  3.     UPDATE TABLE t1;

  4.     UPDATE TABLE t2;

  5.     CALL PROCEDURE B;

  6.   END;

  7.  

  8.  

  9.   PROCEDURE B

  10.   BEGIN

  11.     UPDATE TABLE t3;

  12.   END;



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

Code
  1. PROCEDURE A

  2.   BEGIN

  3.     UPDATE TABLE t1;

  4.     UPDATE TABLE t2;

  5.     CALL PROCEDURE B;

  6.   END;

  7.  

  8.  

  9.   PROCEDURE B

  10.   BEGIN

  11.     UPDATE TABLE t3;

  12.   EXCEPTION

  13.     WHEN OTHERS THEN

  14.       NULL;

  15.   END;

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions