Skip to Main Content

SQL Developer

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

SAVEPOINT and ROLLBACK TO SAVEPOINT Behaviour

943310Jun 13 2012 — edited Jun 22 2012
Hi,
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
This post has been answered by mschwabe on Jun 13 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 20 2012
Added on Jun 13 2012
3 comments
4,454 views