SAVEPOINT and ROLLBACK TO SAVEPOINT Behaviour
943310Jun 13 2012 — edited Jun 22 2012Hi,
I am not able ti understand the behaviour of ROLLBACK TO SAVEPOINT in the below codes.
Scenario 1 :
DECLARE
Var_num NUMBER;
BEGIN
Var_num := 10;
SAVEPOINT A;
DBMS_OUTPUT.PUT_LINE('At SAVEPOINT A');
Var_num := 20;
SAVEPOINT B;
DBMS_OUTPUT.PUT_LINE('At SAVEPOINT B');
Var_num := 30;
SAVEPOINT C;
DBMS_OUTPUT.PUT_LINE('At SAVEPOINT C');
IF (Var_num = 10) THEN
DBMS_OUTPUT.PUT_LINE('Inside IF');
ROLLBACK TO B;
DBMS_OUTPUT.PUT_LINE(Var_num);
ELSE
DBMS_OUTPUT.PUT_LINE('Inside ELSE');
ROLLBACK TO A;
DBMS_OUTPUT.PUT_LINE(Var_num);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('In Exception block');
END;
When i execute this code i get an output as :
At SAVEPOINT A
At SAVEPOINT B
At SAVEPOINT C
Inside ELSE
30
When my code enters into the ELSE state i assume that ROLLBACK TO A will be executed and then the value of Var_num would be 10 with all subsequent savepoints flushed out. Instead what i encounter is that it goes through the piece of code again and fetches Var_num as 30. Can anyone please explain why is this behaviour.
Scenario 2 :
DECLARE
Var_num NUMBER;
BEGIN
Var_num := 10;
SAVEPOINT A;
Var_num := 20;
SAVEPOINT B;
Var_num := 30;
SAVEPOINT C;
Var_num := 40;
ROLLBACK TO A;
DBMS_OUTPUT.PUT_LINE(Var_num);
ROLLBACK TO C;
DBMS_OUTPUT.PUT_LINE(Var_num);
END;
In this case i get an compilation error that 'SAVEPOINT C never established' which i understand is because of the first ROLLBACK TO A statement which flushes out subsequent Savepoints.
Please let me know if mu understanding is correct for Scenario 2 and why i am getting dubious results for scenario 1.
Any help would be highly appreciated.
Regards,
Nikhil