Skip to Main Content
  • Questions

  • Rollback to save-point rollbacks everything

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Arun.

Asked: January 31, 2018 - 2:58 pm UTC

Last updated: March 13, 2018 - 2:22 am UTC

Version: 11G r2

Viewed 1000+ times

You Asked

Hi,

In the below example, "rollback to b;" rollbacks everything instead of clearing only emp3 entries.
----------
create table emp1(empno number,ename char(4),sal number);
create table emp2(empno number,ename char(4),sal number);
create table emp3(empno number,ename char(4),sal number);

insert into emp1(empno,ename,sal) values (109,'Sami',3000);
savepoint a;
insert into emp2(empno,ename,sal) values (109,'Sami',3000);
savepoint b;
insert into emp3(empno,ename,sal) values (109,'Sami',3000);

rollback to b;

-----------

Thanks

and Chris said...

You're going to have to give us some more evidence this is undoing everything.

When I run it, only the rows from emp3 are rolled back, as expected:

SQL> create table emp1(empno number,ename char(4),sal number);

Table EMP1 created.

SQL> create table emp2(empno number,ename char(4),sal number);

Table EMP2 created.

SQL> create table emp3(empno number,ename char(4),sal number);

Table EMP3 created.

SQL>
SQL> insert into emp1(empno,ename,sal) values (109,'Sami',3000);

1 row inserted.

SQL> savepoint a;

Savepoint created.

SQL> insert into emp2(empno,ename,sal) values (109,'Sami',3000);

1 row inserted.

SQL> savepoint b;

Savepoint created.

SQL> insert into emp3(empno,ename,sal) values (109,'Sami',3000);

1 row inserted.

SQL> rollback to b;

Rollback complete.

SQL>
SQL> select * from emp1;
  EMPNO ENAME      SAL
    109 Sami      3000


SQL> select * from emp2;
  EMPNO ENAME      SAL
    109 Sami      3000


SQL> select * from emp3;

no rows selected

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

This seems to be SQL developer problem

Arunkumar, January 31, 2018 - 6:44 pm UTC

This is not working in SQL Developer version 2.1.1.64
(O/P:(rollbacks everything)
1 rows inserted
savepoint a succeeded.
1 rows inserted
savepoint b succeeded.
1 rows inserted
Rolledback
)

This is working in SQL Developer version 4.0.1.14
(O/P:(rollbacks only upto savepoint b)
1 rows inserted.
savepoint a
1 rows inserted.
savepoint b
1 rows inserted.
rollback complete.)

The only difference I see it the last statement in the o/p screen "Rolledback" from old SQL developer and "rollback complete." from new SQL developer.

Weird.....
Chris Saxon
February 01, 2018 - 10:38 am UTC

If that's the case, there's an easy fix then ;)

Savepoint

A reader, March 11, 2018 - 4:44 am UTC

With regards to below queries in livesql.oracle.com,
why savepoint statement shows up "Invalid Statement" error?

create table emp1(empno number,ename char(4),sal number);
create table emp2(empno number,ename char(4),sal number);
create table emp3(empno number,ename char(4),sal number);

insert into emp1(empno,ename,sal) values (109,'Sami',3000);
savepoint a;

Connor McDonald
March 13, 2018 - 2:22 am UTC

Looks like an omission or bug. I'll log it with the LiveSQL team.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library