Re: SAVEPOINT... ROLLBACK question

Ubiquitous wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote:
>
> : You don't provide enough information but ...
> :
> : create table test (
> : testcol varchar2(10));
> : SQL> savepoint a;
> : Savepoint created.
> : SQL> insert into test values ('A');
> : 1 row created.
> : SQL> savepoint b;
> : Savepoint created.
> : SQL> insert into test values ('B');
> : 1 row created.
> : SQL> rollback to a;
> : Rollback complete.
> : SQL> select count(*) from test;
> : COUNT(*)
> : ----------
> : 0
> : What do yo think it should be doing?
>
> That's what I am expecting to happen, but when I call the rollback statement,
> it doesn't roll all the way back to SAVEPOINT A. I suspect there's a commit
> in the stored procedure step, but wouldn't using ROLLBACK TO A override it?

Absolutely not. Overriding a commit is not possible in any SQL RDBMS.

> What would happen if it didn't include the CREATE TABLE statement and
> started with SAVEPONT A instead? I'll have to experiment some more.

All DDL includes to commit statements. One before the DDL and one after. And these will commit any uncommited records.

But you should NEVER issue such DDL in a procedure. Doing so is bad design by definition. If you think you need to you are incorrect and need to put to the group the business problem you think you are trying to solve.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)