Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.icl.net!newsfeed.fjserv.net!colt.net!news.tele.dk!feed118.news.tele.dk!postnews.google.com!e56g2000cwe.googlegroups.com!not-for-mail
From: "Ed Prochak" <edprochak@gmail.com>
Newsgroups: comp.databases.oracle.misc
Subject: Re: Rolling back sequences?
Date: 4 Apr 2006 11:22:22 -0700
Organization: http://groups.google.com
Lines: 53
Message-ID: <1144174942.093822.302540@e56g2000cwe.googlegroups.com>
References: <1144120539.296116.250100@e56g2000cwe.googlegroups.com>
NNTP-Posting-Host: 70.231.97.194
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1144174955 8980 127.0.0.1 (4 Apr 2006 18:22:35 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 4 Apr 2006 18:22:35 +0000 (UTC)
In-Reply-To: <1144120539.296116.250100@e56g2000cwe.googlegroups.com>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0),gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.0 proxy01.ta.com:3128 (squid/2.5.STABLE10)
Complaints-To: groups-abuse@google.com
Injection-Info: e56g2000cwe.googlegroups.com; posting-host=70.231.97.194;
   posting-account=z8J2OwwAAABzrSXAjma7SXOGeLMZZ_s1
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:126396


dean wrote:
> Is it possible to roll back a sequence to its original point before a
> transaction started, if the transaction fails? I need to be able to log
> out reproducible SQL from a complex precedure. Oracle 9.2, windows
> platform.

Can you explain your goal a little better?

If by "I need to be able to log
out reproducible SQL from a complex precedure."
you mean you need to get to get EXACTLY the same results each run,
    then you must essentially rollback the entire database. A restore
from backup before each test run will do the trick. Of course, if there
are any date or time values involves, you might also need to turn back
the CPU clock. Maybe a few other things too.

OTOH if you mean you need to logically reproduce the same results each
run,
   then logically sequence number 5 is no different from sequence
number 6, so why reset the sequence number back?


>
> Here's a typical example:
>
> select train_route_seq.nextval from dual;
>
>    NEXTVAL
> ----------
>     311845
>
> 1 row selected.
>
> rollback
>
> Rollback complete.
>
> select train_route_seq.nextval from dual;
>
>   NEXTVAL
> ----------
>     311846
>
> 1 row selected.
>
> Thanks,
> 
> Dean

The DB works as expected. Why do you want to break it?
   Ed